Thread: EXECUTE query INTO problem

    I've got a problem with a function: It receives two parameters, the 
first, the table name, and the second, a where condition. The function 
looks like this:
   DECLARE      cod bigint;      query TEXT;
   BEGIN      query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2;
      EXECUTE query INTO cod;      ·      ·      ·   END;
   I've alwais get the same error, in the EXECUTE sentence: it says: 
Error at or near NULL at character X
   I've also tried declaring cod as row, but the error is the same.
   Anybody can help me?
   Thank you very much

Tk421 <vrobador@gmail.com> schrieb:

>    I've got a problem with a function: It receives two parameters, the  
> first, the table name, and the second, a where condition. The function  
> looks like this:
>
>    DECLARE
>       cod bigint;
>       query TEXT;
>
>    BEGIN
>       query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2;
>
>       EXECUTE query INTO cod;
>       ·
>       ·
>       ·
>    END;
>
>    I've alwais get the same error, in the EXECUTE sentence: it says:  
> Error at or near NULL at character X

Wild guess: one or both parameters contains nothing, NULL. If you concat
a string with NULL, the result is NULL. And you can't execute a
NULL-command.



>
>    I've also tried declaring cod as row, but the error is the same.
>
>    Anybody can help me?

Please show us the complete function and how do you call this function.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

2008/11/25 Tk421 <vrobador@gmail.com>:
>   I've got a problem with a function: It receives two parameters, the first,
> the table name, and the second, a where condition. The function looks like
> this:
>
>   DECLARE
>      cod bigint;
>      query TEXT;
>
>   BEGIN
>      query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2;
>
>      EXECUTE query INTO cod;
>      ·
>      ·
>      ·
>   END;
>
>   I've alwais get the same error, in the EXECUTE sentence: it says: Error at
> or near NULL at character X
>
>   I've also tried declaring cod as row, but the error is the same.
>
>   Anybody can help me?
>
>   Thank you very much
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


CREATE OR REPLACE FUNCTION test(character varying, character varying)RETURNS integer AS
$BODY$declarer_int int;q      varchar;
begin

if $1 is not null and $2 is not null then
      q = 'select p1 from '||$1||' where '||$2;      execute q into r_int;      return r_int;
else      return null;
end if;
end;$BODY$LANGUAGE 'plpgsql' VOLATILECOST 100;
ALTER FUNCTION test(character varying, character varying) OWNER TO merlin;

and simple table ;]

merlin=> \d t1           Table "public.t1"Column |         Type          | Modifiers
--------+-----------------------+-----------p1     | integer               |p2     | character varying(32) |

merlin=> insert into t1 values(2, 'abc');INSERT 0 1
merlin=> select test('t1', ' p2= ''abc''')

                       ;test
------  2
(1 row)

merlin=> select test(null, ' p2= ''abc''')

                       ;test
------

(1 row)


And all its works


--
Serdecznie pozdrawiam

Pawel Socha
pawel.socha@gmail.com

programista/administrator

perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'