temp table creation
What's wrong with this function? The syntax is supposed to be right,
isn't it?
Create function UsaTablaTemporal()
RETURNS integer
AS '
BEGIN
SELECT p.apellidos, p.nombres, u.username
INTO TEMP TABLE mitabla
FROM persona p, usuario u
WHERE p.idpersona = u.idusuario
AND p.idpersona = 3278;
RETURN 1;
END;'
LANGUAGE 'plpgsql';
If I run:
select usatablatemporal();
I get the message:
ERROR: parser: parse error at or near "temp"
Thanx
Alfonso Peniche <alfonso@iteso.mx> writes:
What's wrong with this function? The syntax is supposed to be right,
isn't it?
No, it isn't right, not for plpgsql. SELECT INTO means something
different to plpgsql than it does in normal Postgres SQL.
regards, tom lane
In that case, how could I create a temporary table from plpgsql? (I don't
want to use select into table.......)
Thanx
Tom Lane wrote:
Show quoted text
Alfonso Peniche <alfonso@iteso.mx> writes:
What's wrong with this function? The syntax is supposed to be right,
isn't it?No, it isn't right, not for plpgsql. SELECT INTO means something
different to plpgsql than it does in normal Postgres SQL.regards, tom lane
Alfonso Peniche <alfonso@iteso.mx> writes:
In that case, how could I create a temporary table from plpgsql?
CREATE [TEMP] TABLE foo AS SELECT ...
This is equivalent to SELECT INTO [TEMP] foo in regular SQL, and
does not change meaning in plpgsql.
regards, tom lane
This should be in the docs somewhere. Is it?
Alfonso Peniche <alfonso@iteso.mx> writes:
In that case, how could I create a temporary table from plpgsql?
CREATE [TEMP] TABLE foo AS SELECT ...
This is equivalent to SELECT INTO [TEMP] foo in regular SQL, and
does not change meaning in plpgsql.regards, tom lane
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Yes Bruce, IT IS In the new documentation (7.1 release), but not in previous
versions. If you want me to be specific, it's in the reference manual under
SQL Commands - CREATE TABLE AS.....
Cheers
Bruce Momjian wrote:
Show quoted text
This should be in the docs somewhere. Is it?
Alfonso Peniche <alfonso@iteso.mx> writes:
In that case, how could I create a temporary table from plpgsql?
CREATE [TEMP] TABLE foo AS SELECT ...
This is equivalent to SELECT INTO [TEMP] foo in regular SQL, and
does not change meaning in plpgsql.regards, tom lane
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Yes Bruce, IT IS In the new documentation (7.1 release), but not in previous
versions. If you want me to be specific, it's in the reference manual under
SQL Commands - CREATE TABLE AS.....Cheers
Great. Seemed like a nifty trick to get around a parser problem.
Bruce Momjian wrote:
This should be in the docs somewhere. Is it?
Alfonso Peniche <alfonso@iteso.mx> writes:
In that case, how could I create a temporary table from plpgsql?
CREATE [TEMP] TABLE foo AS SELECT ...
This is equivalent to SELECT INTO [TEMP] foo in regular SQL, and
does not change meaning in plpgsql.regards, tom lane
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Bruce Momjian <pgman@candle.pha.pa.us> writes:
This should be in the docs somewhere. Is it?
Actually, I thought the question was not about whether CREATE TABLE AS
is documented, but about the specific point of it being the thing
to use for this purpose in plpgsql:
In that case, how could I create a temporary table from plpgsql?
CREATE [TEMP] TABLE foo AS SELECT ...
This is equivalent to SELECT INTO [TEMP] foo in regular SQL, and
does not change meaning in plpgsql.
I have been thinking that our plain-SQL "SELECT INTO table" construct
should be deprecated (and maybe eventually removed), since it really
doesn't have much to do with the SQL-standard meaning of SELECT INTO:
the spec contemplates INTO as introducing a list of scalar variables to
be assigned to. plpgsql and ecpg both use this meaning for SELECT INTO.
Would anyone object if I add text to the SELECT INTO ref page that
points this out, and recommends CREATE TABLE AS as the preferred
syntax?
regards, tom lane
Tom Lane writes:
the spec contemplates INTO as introducing a list of scalar variables to
be assigned to. plpgsql and ecpg both use this meaning for SELECT INTO.Would anyone object if I add text to the SELECT INTO ref page that
points this out, and recommends CREATE TABLE AS as the preferred
syntax?
I agree. SELECT INTO is misnamed; it sounds like INSERT ... SELECT.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Tom Lane writes:
the spec contemplates INTO as introducing a list of scalar variables to
be assigned to. plpgsql and ecpg both use this meaning for SELECT INTO.Would anyone object if I add text to the SELECT INTO ref page that
points this out, and recommends CREATE TABLE AS as the preferred
syntax?I agree. SELECT INTO is misnamed; it sounds like INSERT ... SELECT.
Yes.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026