Thread: Passing nulls into PL/pgSQL functions

Hi,

Is it possible to pass a null value into a PL/pgSQL function. For example

CREATE TABLE mytable(id int, name text);

CREATE OR REPLACE FUNCTION myfunc(int, text) returns int  AS '
 DECLARE
   _id ALIAS FOR $1;
   _name ALIAS FOR $2;

 BEGIN
   EXECUTE ''INSERT INTO mytable (id, name) VALUES(''||_id||'',
''''''||_name||'''''')'';

   RETURN _id;
 END

' LANGUAGE 'plpgsql';

Now, this works ok

select myfunc(1, 'foo');

However, this fails

select myfunc(1, null);

Is there a way of doing it such that I can pass a null sometimes?

Thanks for any help

Adam


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney@sghms.ac.uk> writes:
> Is it possible to pass a null value into a PL/pgSQL function.

Certainly.

Your problem is with the EXECUTE, or even more specifically with the
string concatenation expression you're using to build the EXECUTE
expression.  Do you really need an EXECUTE here at all?  If so,
something involving COALESCE would work.  I'd try

EXECUTE ''INSERT ....'' || coalesce(quote_literal(_name), ''NULL'') || '')'';

            regards, tom lane

I want to use a function to generate a list of OIDs to be used in a
subselect.

However, I can't figure out what to return from my function that will
properly work in a WHERE clause.

I tried:
CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
LANGUAGE SQL;

But when I try:
SELECT * FROM foo WHERE id in in (myTest());

I get this error message:
"ERROR: Argument of WHERE must not be a set function"


How can I use a function to generate my subselect? (I want to cal my
function just once, and avoid calling it once per row.)

Thanks,

Drew

a view instead of the function?

Drew Wilson wrote:
> I want to use a function to generate a list of OIDs to be used in a
> subselect.
>
> However, I can't figure out what to return from my function that will
> properly work in a WHERE clause.
>
> I tried:
> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
> LANGUAGE SQL;
>
> But when I try:
> SELECT * FROM foo WHERE id in in (myTest());
>
> I get this error message:
> "ERROR: Argument of WHERE must not be a set function"
>
>
> How can I use a function to generate my subselect? (I want to cal my
> function just once, and avoid calling it once per row.)
>
> Thanks,
>
> Drew
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

I have to insert/update/delete into these tables. If I use views, I'd
have to write rules to handle the write-through operations.

I'd like to avoid that extra code.

Drew

On Friday, May 16, 2003, at 04:54 PM, Dennis Gearon wrote:

> a view instead of the function?
>
> Drew Wilson wrote:
>> I want to use a function to generate a list of OIDs to be used in a
>> subselect.
>> However, I can't figure out what to return from my function that will
>> properly work in a WHERE clause.
>> I tried:
>> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
>> LANGUAGE SQL;
>> But when I try:
>> SELECT * FROM foo WHERE id in in (myTest());
>> I get this error message:
>> "ERROR: Argument of WHERE must not be a set function"
>> How can I use a function to generate my subselect? (I want to cal my
>> function just once, and avoid calling it once per row.)
>> Thanks,
>> Drew
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>> http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>

On Wed, 16 Apr 2003, Drew Wilson wrote:

> I want to use a function to generate a list of OIDs to be used in a
> subselect.
>
> However, I can't figure out what to return from my function that will
> properly work in a WHERE clause.
>
> I tried:
> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
> LANGUAGE SQL;
>
> But when I try:
> SELECT * FROM foo WHERE id in in (myTest());

I think the syntax would be:
 select * from foo where id in (select * from myTest())

> I get this error message:
> "ERROR: Argument of WHERE must not be a set function"
>
>
> How can I use a function to generate my subselect? (I want to cal my
> function just once, and avoid calling it once per row.)

I think 7.4 might let you get away with calling the function only once for
the above, but current versions don't AFAIK.  I assume the actual
conditions are more complicated than the above (which could probably be
reformulated into a join manually).

Oh, also, regarding using a view... I need the SQL statement to pass a
variable to the function, which I'm not sure I can do with a view.

So my example would be more like:
CREATE FUNCTION myTest(text) RETURNS SETOF oid AS 'SELECT id FROM foo
WHERE name = $1;' LANGUAGE SQL;
and
SELECT * FROM foo WHERE id in in (myTest("bar"));

Thanks,

Drew

On Wednesday, April 16, 2003, at 04:58 PM, Drew Wilson wrote:

> I have to insert/update/delete into these tables. If I use views, I'd
> have to write rules to handle the write-through operations.
>
> I'd like to avoid that extra code.
>
> Drew
>
> On Friday, May 16, 2003, at 04:54 PM, Dennis Gearon wrote:
>
>> a view instead of the function?
>>
>> Drew Wilson wrote:
>>> I want to use a function to generate a list of OIDs to be used in a
>>> subselect.
>>> However, I can't figure out what to return from my function that
>>> will properly work in a WHERE clause.
>>> I tried:
>>> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
>>> LANGUAGE SQL;
>>> But when I try:
>>> SELECT * FROM foo WHERE id in in (myTest());
>>> I get this error message:
>>> "ERROR: Argument of WHERE must not be a set function"
>>> How can I use a function to generate my subselect? (I want to cal my
>>> function just once, and avoid calling it once per row.)
>>> Thanks,
>>> Drew
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 6: Have you searched our list archives?
>>> http://archives.postgresql.org
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to
>> majordomo@postgresql.org
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>

Thank you very much. Yes, "select * from foo where id in (select * from
myTest())" is the syntax I was looking for.

On Wednesday, April 16, 2003, at 05:12 PM, Stephan Szabo wrote:
> On Wed, 16 Apr 2003, Drew Wilson wrote:
>
>> I want to use a function to generate a list of OIDs to be used in a
>> subselect.
>>
>> However, I can't figure out what to return from my function that will
>> properly work in a WHERE clause.
>>
>> I tried:
>> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
>> LANGUAGE SQL;
>>
>> But when I try:
>> SELECT * FROM foo WHERE id in in (myTest());
>
> I think the syntax would be:
>  select * from foo where id in (select * from myTest())
>
>> I get this error message:
>> "ERROR: Argument of WHERE must not be a set function"
>>
>> How can I use a function to generate my subselect? (I want to cal my
>> function just once, and avoid calling it once per row.)
>
> I think 7.4 might let you get away with calling the function only once
> for
> the above, but current versions don't AFAIK.  I assume the actual
> conditions are more complicated than the above (which could probably be
> reformulated into a join manually).

Yes, the SQL function is a join spanning 5 tables, as well as an OR
clause to test for a null relationship at the top.

Thanks again,

Drew

On Wed, 16 Apr 2003, Drew Wilson wrote:

> Thank you very much. Yes, "select * from foo where id in (select * from
> myTest())" is the syntax I was looking for.
>
> On Wednesday, April 16, 2003, at 05:12 PM, Stephan Szabo wrote:
> > On Wed, 16 Apr 2003, Drew Wilson wrote:
> >
> >> I want to use a function to generate a list of OIDs to be used in a
> >> subselect.
> >>
> >> However, I can't figure out what to return from my function that will
> >> properly work in a WHERE clause.
> >>
> >> I tried:
> >> CREATE FUNCTION myTest() RETURNS SETOF oid AS 'SELECT id FROM foo;'
> >> LANGUAGE SQL;
> >>
> >> But when I try:
> >> SELECT * FROM foo WHERE id in in (myTest());
> >
> > I think the syntax would be:
> >  select * from foo where id in (select * from myTest())
> >
> >> I get this error message:
> >> "ERROR: Argument of WHERE must not be a set function"
> >>
> >> How can I use a function to generate my subselect? (I want to cal my
> >> function just once, and avoid calling it once per row.)
> >
> > I think 7.4 might let you get away with calling the function only once
> > for
> > the above, but current versions don't AFAIK.  I assume the actual
> > conditions are more complicated than the above (which could probably be
> > reformulated into a join manually).
>
> Yes, the SQL function is a join spanning 5 tables, as well as an OR
> clause to test for a null relationship at the top.

I wasn't worried about the function (per-se) but the usage.
Select * from foo where id in (select id from myTest())

seems to me anyway pretty equivalent (excepting any possible null related
wierdness) to something like:
select * from foo, (select * from myTest()) bar where foo.id=bar.id

Which should only call the function once.