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
PostgreSQL returns a table with one column that holds the array of films. In practice, you often process each individual row before appending it in the function's result set. The following example illustrates the idea.
You can call a PostgreSQL stored procedure and process a result set in a . NET application, for example, in C# application using Npgsql . NET data provider. Note that you do not need to know the name of the cursor to process the result set.
It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts. The body of a function happens to be such a string literal. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to avoid escaping of nested single quotes (recursively).
RETURN QUERY appends the results of executing a query to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With