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 :

enter image description here

Please help me in this, Thanks in Advance.

Please link back to prior related questions when you write a follow-up. It helps give the reader context about what you're taking about. In this case you really should've included how you ran the function and your exact PostgreSQL version. — Craig Ringer
– Craig Ringer, Commented Jun 6, 2013 at 6:51

Pavel Stehule · Accepted Answer · 2013-06-06 08:51:39Z

5

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;

edited Jun 6, 2013 at 8:51

answered Jun 6, 2013 at 6:04

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anvesh Over a year ago

thanks, but still this return comma separated values. please help in this, I am new in postgresql.

Craig Ringer Over a year ago

Some rather important context that @Anu left out is in this previous question: stackoverflow.com/q/16940583/398670 . You'll notice that there I used the format function's %I format-specifier for identifier substitution and mentioned EXECUTE ... USING.

Pavel Stehule Over a year ago

@Anu what is your code on client side? Maybe is a problem there.

Anvesh Over a year ago

ok , thanks I got solution , need to just select *from function()

Collectives™ on Stack Overflow

Return Table using Dynamic SQL Query in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related