Thread

1. Select in function?

   Alfonso Peniche <alfonso@iteso.mx> — 2001-02-07T00:33:15Z

   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.
   
   
   

2. Re: Select in function?

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-02-07T00:42:33Z

   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.
   > 
   
   
   

3. Re: Select in function?

   Andrew Snow <andrew@modulus.org> — 2001-02-07T00:49:09Z

   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.
   
   
   
   

4. Re: Select in function?

   Alfonso Peniche <alfonso@iteso.mx> — 2001-02-07T17:45:52Z

   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.