Обсуждение: Select in function?

I have the following function:

CREATE FUNCTION RegistrarDesconexion( integer )
RETURNS integer
AS '
    BEGIN
        UPDATE BitacoraConexion
        SET Desconexion = CURRENT_TIMESTAMP
        WHERE IdBitacoraConexion = $1;
        SELECT Desconexion FROM BitacoraConexion
        WHERE IdBitacoraConexion = $1;
        IF FOUND THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;'
LANGUAGE 'plpgsql';

and if I run:
select registrardesconexion (5);

I get the following error:
ERROR:  unexpected SELECT query in exec_stmt_execsql()

I don't know what's wrong, though I assume it's saying I'm not allowed
to use the select statement where it is.

Any ideas or comments.

Thanx.

It wants you to do a
SELECT INTO <var> ...
in the function, something like

'DECLARE
  rec RECORD
 BEGIN
  ...
  SELECT INTO rec Desconexion FROM BitacoraConexion
  ...
'


On Tue, 6 Feb 2001, Alfonso Peniche wrote:

> I have the following function:
>
> CREATE FUNCTION RegistrarDesconexion( integer )
> RETURNS integer
> AS '
>     BEGIN
>         UPDATE BitacoraConexion
>         SET Desconexion = CURRENT_TIMESTAMP
>         WHERE IdBitacoraConexion = $1;
>         SELECT Desconexion FROM BitacoraConexion
>         WHERE IdBitacoraConexion = $1;
>         IF FOUND THEN
>             RETURN 1;
>         ELSE
>             RETURN 0;
>         END IF;
>     END;'
> LANGUAGE 'plpgsql';
>
> and if I run:
> select registrardesconexion (5);
>
> I get the following error:
> ERROR:  unexpected SELECT query in exec_stmt_execsql()
>
> I don't know what's wrong, though I assume it's saying I'm not allowed
> to use the select statement where it is.
>
> Any ideas or comments.
>
> Thanx.
>

As per the documentation at:
http://postgresql.planetmirror.com/users-lounge/docs/7.0/user/c40874113.htm#AEN4207

When using SELECT in a PL/PGSQL function, you must either SELECT .. INTO or
use the PERFORM query if you don't care about the return value from a
SELECT.  If you wish to use the special FOUND variable, you have to SELECT
.. INTO.

Hope that helps,

Andrew.


On Tue, 6 Feb 2001, Alfonso Peniche wrote:

> I have the following function:
>
> CREATE FUNCTION RegistrarDesconexion( integer )
> RETURNS integer
> AS '
>     BEGIN
>         UPDATE BitacoraConexion
>         SET Desconexion = CURRENT_TIMESTAMP
>         WHERE IdBitacoraConexion = $1;
>         SELECT Desconexion FROM BitacoraConexion
>         WHERE IdBitacoraConexion = $1;
>         IF FOUND THEN
>             RETURN 1;
>         ELSE
>             RETURN 0;
>         END IF;
>     END;'
> LANGUAGE 'plpgsql';
>
> and if I run:
> select registrardesconexion (5);
>
> I get the following error:
> ERROR:  unexpected SELECT query in exec_stmt_execsql()
>
> I don't know what's wrong, though I assume it's saying I'm not allowed
> to use the select statement where it is.

Thanks all, it's working great now.

Cheers. :-)

andrew@modulus.org wrote:

> As per the documentation at:
> http://postgresql.planetmirror.com/users-lounge/docs/7.0/user/c40874113.htm#AEN4207
>
> When using SELECT in a PL/PGSQL function, you must either SELECT .. INTO or
> use the PERFORM query if you don't care about the return value from a
> SELECT.  If you wish to use the special FOUND variable, you have to SELECT
> .. INTO.
>
> Hope that helps,
>
> Andrew.
>
> On Tue, 6 Feb 2001, Alfonso Peniche wrote:
>
> > I have the following function:
> >
> > CREATE FUNCTION RegistrarDesconexion( integer )
> > RETURNS integer
> > AS '
> >     BEGIN
> >         UPDATE BitacoraConexion
> >         SET Desconexion = CURRENT_TIMESTAMP
> >         WHERE IdBitacoraConexion = $1;
> >         SELECT Desconexion FROM BitacoraConexion
> >         WHERE IdBitacoraConexion = $1;
> >         IF FOUND THEN
> >             RETURN 1;
> >         ELSE
> >             RETURN 0;
> >         END IF;
> >     END;'
> > LANGUAGE 'plpgsql';
> >
> > and if I run:
> > select registrardesconexion (5);
> >
> > I get the following error:
> > ERROR:  unexpected SELECT query in exec_stmt_execsql()
> >
> > I don't know what's wrong, though I assume it's saying I'm not allowed
> > to use the select statement where it is.