pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedtemp table creation
10+ messages / 4 participants
[nested] [flat]
* temp table creation
@ 2001-02-15 01:09 Alfonso Peniche <alfonso@iteso.mx>
0 siblings, 1 reply; 10+ messages in thread
From: Alfonso Peniche @ 2001-02-15 01:09 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: temp table creation
@ 2001-02-15 01:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alfonso Peniche <alfonso@iteso.mx>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2001-02-15 01:54 UTC (permalink / raw)
To: Alfonso Peniche <alfonso@iteso.mx>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: temp table creation
@ 2001-02-15 15:26 Alfonso Peniche <alfonso@iteso.mx>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alfonso Peniche @ 2001-02-15 15:26 UTC (permalink / raw)
To: ; +Cc: pgsql-general@postgresql.org
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:
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Re: temp table creation
@ 2001-02-15 15:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alfonso Peniche <alfonso@iteso.mx>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2001-02-15 15:35 UTC (permalink / raw)
To: Alfonso Peniche <alfonso@iteso.mx>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [GENERAL] Re: temp table creation
@ 2001-03-20 00:17 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2001-03-20 00:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alfonso Peniche <alfonso@iteso.mx>; pgsql-docs
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [GENERAL] Re: temp table creation
@ 2001-03-20 16:28 Alfonso Peniche <alfonso@iteso.mx>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alfonso Peniche @ 2001-03-20 16:28 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
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
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [GENERAL] Re: temp table creation
@ 2001-03-20 16:47 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Alfonso Peniche <alfonso@iteso.mx>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2001-03-20 16:47 UTC (permalink / raw)
To: Alfonso Peniche <alfonso@iteso.mx>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
> 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 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Re: [GENERAL] Re: temp table creation
@ 2001-03-20 17:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2001-03-20 17:14 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Alfonso Peniche <alfonso@iteso.mx>; pgsql-docs
Bruce 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Re: [GENERAL] Re: temp table creation
@ 2001-03-20 18:02 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2001-03-20 18:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Alfonso Peniche <alfonso@iteso.mx>; pgsql-docs
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Re: [GENERAL] Re: temp table creation
@ 2001-03-20 18:32 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2001-03-20 18:32 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alfonso Peniche <alfonso@iteso.mx>; pgsql-docs
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-03-20 18:32 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-15 01:09 temp table creation Alfonso Peniche <alfonso@iteso.mx>
2001-02-15 01:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-15 15:26 ` Alfonso Peniche <alfonso@iteso.mx>
2001-02-15 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-20 00:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-20 16:28 ` Alfonso Peniche <alfonso@iteso.mx>
2001-03-20 16:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-20 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-20 18:02 ` Peter Eisentraut <peter_e@gmx.net>
2001-03-20 18:32 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox