Create function with temporary tables that return a select query using these temp tables
NickName:postgresql_beginner Ask DateTime:2017-09-24T19:21:11

Create function with temporary tables that return a select query using these temp tables

I need to create a function, which returns results of a SELECT query. This SELECT query is a JOIN of few temporary tables created inside this function. Is there any way to create such function? Here is an example (it is very simplified, in reality there are multiple temp tables with long queries):

CREATE OR REPLACE FUNCTION myfunction () RETURNS TABLE (column_a TEXT, column_b TEXT) AS $$
BEGIN
CREATE TEMPORARY TABLE raw_data ON COMMIT DROP
AS
SELECT d.column_a, d2.column_b FROM dummy_data d JOIN dummy_data_2 d2 using (id);

RETURN QUERY (select distinct column_a, column_b from raw_data limit 100);
END;
$$
LANGUAGE 'plpgsql' SECURITY DEFINER

I get error:

[Error] Script lines: 1-19 -------------------------
 ERROR: RETURN cannot have a parameter in function returning set;
 use RETURN NEXT at or near "QUERY"Position: 237

I apologize in advance for any obvious mistakes, I'm new to this.

Psql version is PostgreSQL 8.2.15 (Greenplum Database 4.3.12.0 build 1)

Copyright Notice:Content Author:「postgresql_beginner」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/46389505/create-function-with-temporary-tables-that-return-a-select-query-using-these-tem

Answers
A. Scherbaum 2017-09-25T12:22:36

The most recent version of Greenplum Database (5.0) is based on PostgreSQL 8.3, and it supports the RETURN QUERY syntax. Just tested your function on:\n\nPostgreSQL 8.4devel (Greenplum Database 5.0.0-beta.10+dev.726.gd4a707c762 build dev)\n",


More about “Create function with temporary tables that return a select query using these temp tables” related questions

Create function with temporary tables that return a select query using these temp tables

I need to create a function, which returns results of a SELECT query. This SELECT query is a JOIN of few temporary tables created inside this function. Is there any way to create such function? Her...

Show Detail

How Can I Run Sequential Temp Tables & Final SELECT Query

I'm used to BigQuery where I can run temp tables with the 'WITH' clause and then join those temp tables with a final query. However, I am now using a Hive db via DataGrip where I cannot run sequent...

Show Detail

MySQLi Temporary Tables With PHP

I want to ask if this approach will work. I have an enormous table called earnings that I often have to query. I'm trying to optimize so that I only query according to the relevant campaign_id. I ...

Show Detail

Creating and using temporary tables

I have just learnt about temporary tables and using them has given me some really nice speed increases in some of my big queries. The problem I have is that when I create the table, it doesn't las...

Show Detail

working with temporary tables in Joomla 2.5

I am trying to use MySql temporary tables with my joomla site. the problem is that whenever I query about the content of the table I get an empty result (except when using select statement in the s...

Show Detail

Need to create a view that utilizes multiple temporary tables

I'm trying to create a view from code that utilizes multiple temporary tables. I am ultimately able to completed this when my temp tables take the form of approach A: Approach A: create view viewte...

Show Detail

Temporary tables and AS statement usage

If I have a query, say: SELECT * FROM ( SELECT fields FROM tables WHERE condition ) AS TEMP_TABLE are the results of the above query saved in a temporary table called TEMP_...

Show Detail

Sql Nested temporary tables

I would like to know if it is possible to create a temporary table within another temporary table, then run a query off the outer nested table. I have a query on 2 joined tables that produce a lis...

Show Detail

Error creating function with temporary tables

I am creating a function that contains a temporary table, it is a bit difficult for me to use the function with a temporary table, I totally do not know if it is allowed within a function since I a...

Show Detail

Create one table out of multiple temp tables using SQL

If it is possible, how to create one table/temp table out of multiple temp tables using SQL? Because creating a view is unfortunately not possible with temp tables. For example, I have 3/more temp ...

Show Detail