Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Alexander Farber
Date:
Good morning,

why does this syntax fail in 9.5.3 please?

I am trying to call 2 custom functions from a third one with:

CREATE OR REPLACE FUNCTION play_game(
        IN in_uid integer,
        IN in_gid integer,
        IN in_tiles jsonb,
        OUT out_gid integer)
        RETURNS integer AS
$func$
DECLARE
        ....
BEGIN
        PERFORM check_positions(in_uid, in_gid, in_tiles);

        SELECT
                out_word AS word,
                max(out_score) AS score
        INTO TEMP TABLE _words ON COMMIT DROP
        FROM check_words(in_uid, in_gid, in_tiles)
        GROUP BY word, gid;
...
END
$func$ LANGUAGE plpgsql;

But get the errors (I tried TEMP, TEMPORARY, with and without TABLE):

words=> \i play_game.sql
psql:play_game.sql:166: ERROR:  "temp" is not a known variable
LINE 29:         INTO TEMP TABLE _words ON COMMIT DROP
                      ^

words=> \i play_game.sql
psql:play_game.sql:166: ERROR:  "temporary" is not a known variable
LINE 29:         INTO TEMPORARY TABLE _words ON COMMIT DROP
                      ^


Thank you
Alex

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Francisco Olarte
Date:
On Fri, Aug 12, 2016 at 10:41 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> why does this syntax fail in 9.5.3 please?

Maybe because.......

> $func$ LANGUAGE plpgsql;

... you are writing pspgsql.....

> The doc https://www.postgresql.org/docs/9.5/static/sql-selectinto.html just

But are looking at the docs for SQL. This kind of languages are
similar to SQL, but not the same. I think

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

is the proper place to look it up.

Francisco Olarte.

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Alexander Farber
Date:
Francisco, thanks, but -

On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte <folarte@peoplecall.com> wrote:

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW


but the custom function I am trying to call (from another function) does not return one row, but several rows, which I'd like to store into a temp table:

        SELECT
                out_word AS word,
                max(out_score) AS score
        INTO TEMP TABLE _words ON COMMIT DROP
        FROM check_words(in_uid, in_gid, in_tiles)
        GROUP BY word, gid;

Regards
Alex

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Francisco Olarte
Date:
Alexander:

On Fri, Aug 12, 2016 at 11:00 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> but the custom function I am trying to call (from another function) does not
> return one row, but several rows, which I'd like to store into a temp table:

This I know, I wasn't trying to solve the problem. I was just trying
to point that "select" is not the same in plpgsql and in sql, so you
need to read the docs for plpgsql to find how to solve it.

Francisco Olarte.

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Guillaume Lelarge
Date:
2016-08-12 11:00 GMT+02:00 Alexander Farber <alexander.farber@gmail.com>:
Francisco, thanks, but -

On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte <folarte@peoplecall.com> wrote:

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW


but the custom function I am trying to call (from another function) does not return one row, but several rows, which I'd like to store into a temp table:

        SELECT
                out_word AS word,
                max(out_score) AS score
        INTO TEMP TABLE _words ON COMMIT DROP
        FROM check_words(in_uid, in_gid, in_tiles)
        GROUP BY word, gid;


Francisco is right. SELECT INTO doesn't have the same meaning in SQL and PL/pgsql. If you want to insert the result of the SELECT into a temporary table, create the temp table and insert into it:

CREATE TEMP TABLE...
INSERT INTO your_temp_table SELECT...


--

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Alexander Farber
Date:
Thank you,  I have rewritten it into:

BEGIN
        PERFORM check_positions(in_uid, in_gid, in_tiles);

        CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT DROP;

        INSERT INTO _words
        SELECT
                out_word AS word,
                max(out_score) AS score
        FROM check_words(in_uid, in_gid, in_tiles)
        GROUP BY word, gid;

Regards
Alex

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Craig Ringer
Date:
On 12 August 2016 at 18:43, Alexander Farber <alexander.farber@gmail.com> wrote:
Thank you,  I have rewritten it into:

BEGIN
        PERFORM check_positions(in_uid, in_gid, in_tiles);

        CREATE TEMP TABLE _words(word varchar, score integer) ON COMMIT DROP;

        INSERT INTO _words
        SELECT
                out_word AS word,
                max(out_score) AS score
        FROM check_words(in_uid, in_gid, in_tiles)
        GROUP BY word, gid;


Or use CREATE TABLE ... AS SELECT ... 

That's the SQL-standard spelling anyway. 


--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

From
Alexander Farber
Date:
Thank you Craig, this has worked in my custom function too:

BEGIN
        PERFORM check_positions(in_uid, in_gid, in_tiles);

        CREATE TEMP TABLE _words ON COMMIT DROP AS
        SELECT
                out_word AS word,
                max(out_score) AS score
        FROM check_words(in_uid, in_gid, in_tiles)
        GROUP BY word, gid;

PL/pgSQL is weird, but fun :-) 

I like that I can RAISE EXCEPTION in my custom function and PostgreSQL rolls everything back.

Regards
Alex