CODEWITHSUNDEEP
sqlpostgresql
Anvesh
Anvesh

Reputation: 7713

Return Table using Dynamic SQL Query in PostgreSQL

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 :

enter image description here

Please help me in this, Thanks in Advance.

Upvotes: 3

Views: 6501

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

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;

Upvotes: 5

Related Questions