Обсуждение: SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1
Good morning,
why does this syntax fail in 9.5.3 please?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;
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;
$func$ LANGUAGE plpgsql;
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
^
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
^
The doc https://www.postgresql.org/docs/9.5/static/sql-selectinto.html just says:
" read the doc https://www.postgresql.org/docs/9.5/static/sql-createtable.html "
" read the doc https://www.postgresql.org/docs/9.5/static/sql-createtable.html "
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.
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;
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
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.
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-statement s.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...
--
Thank you, I have rewritten it into:
RegardsBEGIN
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;
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;
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.
Thank you Craig, this has worked in my custom function too:
PL/pgSQL is weird, but fun :-)BEGINPERFORM check_positions(in_uid, in_gid, in_tiles);CREATE TEMP TABLE _words ON COMMIT DROP ASSELECTout_word AS word,max(out_score) AS scoreFROM check_words(in_uid, in_gid, in_tiles)GROUP BY word, gid;
I like that I can RAISE EXCEPTION in my custom function and PostgreSQL rolls everything back.
Regards
Alex