Horton
Menu

Thread

  1. temp table creation

    Alfonso Peniche <alfonso@iteso.mx> — 2001-02-15T01:09:27Z

    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

  2. Re: temp table creation

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-15T01:54:51Z

    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

  3. Re: temp table creation

    Alfonso Peniche <alfonso@iteso.mx> — 2001-02-15T15:26:25Z

    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

  4. Re: Re: temp table creation

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-15T15:35:58Z

    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

  5. Re: [GENERAL] Re: temp table creation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-20T00:17:36Z

    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

  6. Re: [GENERAL] Re: temp table creation

    Alfonso Peniche <alfonso@iteso.mx> — 2001-03-20T16:28:04Z

    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

  7. Re: [GENERAL] Re: temp table creation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-20T16:47:41Z

    > 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

  8. Re: Re: [GENERAL] Re: temp table creation

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-20T17:14:22Z

    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

  9. Re: Re: [GENERAL] Re: temp table creation

    Peter Eisentraut <peter_e@gmx.net> — 2001-03-20T18:02:22Z

    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/

  10. Re: Re: [GENERAL] Re: temp table creation

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-20T18:32:49Z

    > 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