How to return temp table result in postgresql function

Question

I am using a temporary table in a function to save some results however I don't know how to return the table from the function. Ideally I would like to do everything in one query (i.e. not two queries: one for calling the function, the other to get data from the temp table).

Currently my main_function() is as follows:

CREATE OR REPLACE FUNCTION main_function() RETURNS void AS
$BODY$
BEGIN

    DROP TABLE IF EXISTS temp_t CASCADE;
    CREATE TEMP TABLE temp_t AS SELECT * FROM tbl_t limit 0;

    EXECUTE 'INSERT INTO temp_t ' || 'SELECT * FROM tbl_t limit 10';

END;
$BODY$
LANGUAGE 'plpgsql' ;

And I am calling it like so:

SELECT * from main_function();
SELECT * from temp_t;

Again, the problem is that I don't actually want to call the second query. The first query should return the temp table as a result, however I cannot do this since the temp table is created in main_function() so it cannot be its return type.

Any ideas on how to achieve this?

Thanks

cathulhu · Accepted Answer · 2013-07-05 12:38:12Z

10

Inside your main_function():

RETURN QUERY SELECT * FROM temp_t;

...if temp_t table consists of e.g. column1 (type integer), column2 (boolean) and column3 (varchar(100)), you should also define returned type as:

CREATE OR REPLACE FUNCTION main_function(column1 OUT integer, column2 OUT boolean, column3 OUT varchar(100)) RETURNS SETOF record AS
(...)

Another way is to define new data type:

CREATE TYPE temp_t_type AS (
    column1 integer,
    column2 boolean,
    column3 varchar(100)
);

That type can be returned by your functions in the same way as normal data types:

CREATE OR REPLACE FUNCTION main_function() RETURNS SETOF temp_t_type AS
(...)

...and return result from the function in the same way as mentioned above.

edited Jul 5, 2013 at 12:38

answered Jul 5, 2013 at 12:33

cathulhu

6772 gold badges10 silver badges21 bronze badges

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

Comments

Erwin Brandstetter · Accepted Answer · 2013-07-05 17:30:41Z

5

Are you sure you need a temporary table? Most of the time, there is a cheaper solution. Your example can simply be:

CREATE OR REPLACE FUNCTION main_function()
  RETURNS SETOF tbl_t AS
$BODY$
BEGIN

RETURN QUERY EXECUTE 'SELECT * FROM tbl_t LIMIT 10';

END
$BODY$
LANGUAGE plpgsql;

You also don't need EXECUTE or even plpgsql for the simple case:

CREATE OR REPLACE FUNCTION main_function()
  RETURNS SETOF tbl_t AS
$BODY$

SELECT * FROM tbl_t LIMIT 10;

$BODY$
LANGUAGE sql;

Never quote the language name. It's an identifier.

edited Jul 5, 2013 at 17:30

answered Jul 5, 2013 at 13:09

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

user1575120 · Accepted Answer · 2016-01-27 16:25:18Z

2

instead of

CREATE OR REPLACE FUNCTION main_function() RETURNS void AS

use some like CREATE OR REPLACE FUNCTION main_function() RETURNS TABLE(f1 int, f2 text) AS...

answered Jan 27, 2016 at 16:25

user1575120

3592 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to return temp table result in postgresql function

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related