plpgsql how to just execute query in a function or procedure

Question

I am beginning to learn stored procedures and functions in sql in a postgres database. I need an example to get me going for what I am trying to accomplish.

I need to run a procedure and have it return results. For example something like this:

run_query(name):
 begin
 return select * from employees where first_name = $name
 end
end

I want something like the above to return the result set when I run it. Is this possible? thank you for your help in advance! Here is the function im trying to create:

CREATE OR REPLACE FUNCTION test() RETURNS TABLE(id INT, subdomain varchar, launched_on_xxx timestamp, UVs bigint, PVs bigint) AS
 'SELECT dblink_connect(''other_DB'');
 SELECT c.id as id, c.subdomain, c.launched_on_xxx, COALESCE(SUM(tbd.new_unique_visitors), 0) AS UVs, COALESCE(SUM(tbd.page_views), 0) AS PVs
 FROM dblink(''SELECT id, subdomain, launched_on_xxx FROM communities'')
 AS c(id int, subdomain character varying, launched_on_xxx timestamp)
 LEFT OUTER JOIN days_of_center tbd
 ON c.id = tbd.community_id
 WHERE c.launched_on_xxx < now()
 GROUP BY c.id, c.subdomain, c.launched_on_xxx;
 SELECT dblink_disconnect();'
 LANGUAGE SQL;

Answer 1

Your function could look like this:

CREATE OR REPLACE FUNCTION test()
 RETURNS TABLE(id int, subdomain varchar, launched_on_xxx timestamp
 ,uvs bigint, pvs bigint) AS
$func$
SELECT dblink_connect('other_DB');
SELECT c.id
 ,c.subdomain
 ,c.launched_on_xxx
 ,COALESCE(SUM(tbd.new_unique_visitors), 0) AS uvs
 ,COALESCE(SUM(tbd.page_views), 0) AS pvs
FROM dblink('
 SELECT id, subdomain, launched_on_xxx
 FROM communities
 WHERE launched_on_xxx < now()')
 AS c(id int, subdomain varchar, launched_on_xxx timestamp)
LEFT JOIN days_of_center tbd ON tbd.community_id = c.id
GROUP BY c.id, c.subdomain, c.launched_on_xxx;
SELECT dblink_disconnect();
$func$ LANGUAGE SQL;

• Pull the WHERE clause down into the dblink function. It's much more effective not to fetch rows to begin with - instead of fetching them from the external database and then discarding them.

• Use dollar-quoting to avoid confusion with quoting. That has become standard procedure with bigger function definitions.

To output it in "table format", call a function returning multiple columns like this:

SELECT * FROM test();

• Denis de Bernardy: +1, though I'd suggest adding the plpgsql variant, since OP's question references the latter.
• Erwin Brandstetter: @Denis: It's not entirely clear to me what the OP would want, since the first and the second example in the Q are disconnected.

Answer 2

Just about the simplest possible example would be this;

CREATE FUNCTION test() RETURNS TABLE(num INT) AS
 'SELECT id FROM table1'
 LANGUAGE SQL;
SELECT * FROM test()

An SQLfiddle to test with.

If you need a parameter, here's another example;

CREATE FUNCTION test(sel INT) RETURNS TABLE(val VARCHAR) AS
 'SELECT value FROM table1 WHERE id=sel'
 LANGUAGE SQL;
SELECT * FROM test(2)

Another SQLfiddle to test with.

• Kuberchaun: This is the best way. If you can do it in a SQL function do that. If you can't use straight SQL because you need to do procedural logic on the data before you return it you can do it with PL\pgSQL using a RETURNS TABLE construct.
• Doublespeed: Do I need to mention the data types of the return table? I just want to project the columns in the returns clause, not the data types.
• Joachim Isaksson: @user2081579 As far as I know, it's mandatory in all cases to have a RETURNS clause.
• Doublespeed: its returning one column with the data I want in set notation rather than a query output. I see one column called test record. And the data in the column is like this: (72,southsanfrancisco,"2013年05月14日 10:26:51.390466",695108,1620743) I would like the output to be in table format. How do I let it return data as if a normal query
• Joachim Isaksson: @user2081579 I'm not quite sure I understand. Could you add an SQLfiddle with the problem?