Return Table using Dynamic SQL Query in PostgreSQL

Question

Below is my function in which I execute one dynamic query but I got result of this query in one single column and all values are in comma separated.

CREATE OR REPLACE FUNCTION get_weather(city text)
RETURNS weather AS $$
DECLARE
rec RECORD;
BEGIN
EXECUTE 'SELECT * FROM weather WHERE city = ''' || city || '''' INTO rec;
RETURN rec;
END;
$$ LANGUAGE plpgsql;

Result of above function :

"("San Francisco",46,50,0.25,1994-11-27)"

Required output :

Please help me in this, Thanks in Advance.

Answer 1

You have to use SELECT FROM

SELECT * FROM get_weather('Prague');

postgres=# SELECT * FROM weather ;
  city  │ lo │ hi │     d      
────────┼────┼────┼────────────
 San Fr │ 46 │ 50 │ 2013-06-06
(1 row)

postgres=# SELECT * FROM get_weather('San Fr');
  city  │ lo │ hi │     d      
────────┼────┼────┼────────────
 San Fr │ 46 │ 50 │ 2013-06-06
(1 row)

postgres=# \sf get_weather 
CREATE OR REPLACE FUNCTION public.get_weather(city text)
 RETURNS weather
 LANGUAGE plpgsql
AS $function$
DECLARE
rec RECORD;
BEGIN
EXECUTE 'SELECT * FROM weather WHERE city = $1' INTO rec USING sity;
RETURN rec;
END;
$function$

There are some possible optimization in your example

• Use RETURN QUERY EXECUTE
• don't use dynamic query ever - use RETURN QUERY
• use SQL language instead PL/pgSQL for one row functions

CREATE OR REPLACE FUNCTION get_weather(city text)
RETURNS weather AS $$
SELECT * FROM weather WHERE city = $1;
$$ LANGUAGE sql;

Attention: newer build dynamic query like you do! This is exemplary example of SQL injection problem.

Use instead

EXECUTE 'SELECT * FROM weather WHERE city = ' || quote_literal(city) INTO rec;

or better

EXECUTE 'SELECT * FROM weather WHERE city = $1'  INTO rec USING city;