Passing nulls into PL/pgSQL functions

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

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:

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:

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.

a view instead of the function?

Drew Wilson wrote: