CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Doublespeed
Doublespeed

Reputation: 1173

plpgsql how to just execute query in a function or procedure

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;

Upvotes: 1

Views: 1885

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

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.

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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();

Upvotes: 3

Related Questions