Thread: 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:

> 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