Thread: INSERT... RETURNING with a function

I've written a straightforward insert function, but using the
RETURNING keyword for the first time. If I try running the test case
I get the error:

ERROR:  query has no destination for result data
CONTEXT:  PL/pgSQL function "nonauth_users_insert_new_udf" line 7 at
SQL statement

I'm not sure what I need to do to catch the return value as I've
tried a few different combinations and nothing has worked for me so
far. The Insert statement as it is below but outside of a function
works and returns the id. Any input is much appreciated.

I'm running version 8.4

Regards,
Iain


/*test*/
/*
select nonauth_users_insert_new_udf(
'testuser1', 'testuser1@example.com', 'xDvTfTOB99mG6zSyMjYtVhUd3P4',
'4DhFLU1YJU5Oz/+XGqh3npn2RJQ'
);
*/

CREATE OR REPLACE FUNCTION nonauth_users_insert_new_udf(
    _username varchar
  , _emailaddress varchar
  , _salt char
  , _hash char
  ) RETURNS int
AS $$
  BEGIN

   Insert into nonauth_users ( username, emailaddress, salt, hash,
added )
   values ( _username, _emailaddress, _salt, _hash, now() )
   RETURNING nonauth_users_id;

   --the query works this way though
  --RETURN currval(pg_get_serial_sequence('nonauth_users',
'nonauth_users_id'));

  END;

$$
LANGUAGE plpgsql;

On Saturday 26 September 2009 11:04:42 am Iain Barnett wrote:
> I've written a straightforward insert function, but using the
> RETURNING keyword for the first time. If I try running the test case
> I get the error:
>
> ERROR:  query has no destination for result data
> CONTEXT:  PL/pgSQL function "nonauth_users_insert_new_udf" line 7 at
> SQL statement
>
> I'm not sure what I need to do to catch the return value as I've
> tried a few different combinations and nothing has worked for me so
> far. The Insert statement as it is below but outside of a function
> works and returns the id. Any input is much appreciated.
>
> I'm running version 8.4
>
> Regards,
> Iain
>
>
> /*test*/
> /*
> select nonauth_users_insert_new_udf(
> 'testuser1', 'testuser1@example.com', 'xDvTfTOB99mG6zSyMjYtVhUd3P4',
> '4DhFLU1YJU5Oz/+XGqh3npn2RJQ'
> );
> */
>
> CREATE OR REPLACE FUNCTION nonauth_users_insert_new_udf(
>     _username varchar
>   , _emailaddress varchar
>   , _salt char
>   , _hash char
>   ) RETURNS int
> AS $$
>   BEGIN
>
>    Insert into nonauth_users ( username, emailaddress, salt, hash,
> added )
>    values ( _username, _emailaddress, _salt, _hash, now() )
>    RETURNING nonauth_users_id;
>
>    --the query works this way though
>   --RETURN currval(pg_get_serial_sequence('nonauth_users',
> 'nonauth_users_id'));
>
>   END;
>
> $$
> LANGUAGE plpgsql;

See:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

Not tested

CREATE OR REPLACE FUNCTION nonauth_users_insert_new_udf(
    _username varchar
  , _emailaddress varchar
  , _salt char
  , _hash char
  ) RETURNS int
AS $$
  DECLARE
    id_val int;
  BEGIN

   Insert into nonauth_users ( username, emailaddress, salt, hash,  
added )
   values ( _username, _emailaddress, _salt, _hash, now() )
   RETURNING nonauth_users_id INTO id_val;

   RETURN id_val;

   --the query works this way though
  --RETURN currval(pg_get_serial_sequence('nonauth_users',  
'nonauth_users_id'));

  END;

$$
LANGUAGE plpgsql;


--
Adrian Klaver
aklaver@comcast.net

Iain Barnett <iainspeed@gmail.com> writes:
> I've written a straightforward insert function, but using the
> RETURNING keyword for the first time. If I try running the test case
> I get the error:

> ERROR:  query has no destination for result data
> CONTEXT:  PL/pgSQL function "nonauth_users_insert_new_udf" line 7 at
> SQL statement

I think you want INSERT ... RETURNING ... INTO some-plpgsql-variable;

            regards, tom lane

I think you want INSERT ... RETURNING ... INTO some-plpgsql-variable;

regards, tom lane