SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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...

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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

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

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