pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNull parm to a function
2+ messages / 2 participants
[nested] [flat]
* Null parm to a function
@ 2000-06-17 05:15 Gordon Clarke <gordonc@acenet.net.au>
2000-06-17 05:46 ` Re: Null parm to a function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Gordon Clarke @ 2000-06-17 05:15 UTC (permalink / raw)
To: pgsql-sql
I want to be able to pass NULL as a parameter to a function I've defined,
but I'm not allowed to do it. I believe this is a bug.
The example follows:
CREATE TABLE test_func(
func_sun int4,
surname varchar(30),
value int2 NOT NULL,
CONSTRAINT p01_test_func PRIMARY KEY(func_sun)
);
This insert works as one would expect.
INSERT INTO test_func(func_sun, surname, value)
VALUES(1234,null,99);
Now create the function that will insert rows into my table test_func.
CREATE FUNCTION insert_row(int4,varchar,int2)
RETURNS int4 AS
'INSERT INTO test_func(func_sun, surname, value)
VALUES($1,$2,$3);
SELECT 1 AS ignore;'
LANGUAGE 'sql';
Now if I try to do the same thing but via my function,
SELECT insert_row(1235,null,100);
I get the following error
ERROR: typeidTypeRelid: Invalid type - oid = 0
Why wont the function allow me to pass a 'null' parm?
Cheers...Gordon
~~~~~~~~~~~~~~ 4ZzZ Brisbane's First Community FM Station ~~~~~~~~~~~~~~
The Demo Show - Supporting Australian Unsigned Artists
Mon nights 6-7pm (0800-0900UTC) on 4ZzZ 102.1 FM
http://www.4zzzfm.org.au/zed (RA feed)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Null parm to a function
2000-06-17 05:15 Null parm to a function Gordon Clarke <gordonc@acenet.net.au>
@ 2000-06-17 05:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-06-17 05:46 UTC (permalink / raw)
To: gordonc@acenet.net.au; +Cc: pgsql-sql
Gordon Clarke <gordonc@acenet.net.au> writes:
> Now if I try to do the same thing but via my function,
> SELECT insert_row(1235,null,100);
> I get the following error
> ERROR: typeidTypeRelid: Invalid type - oid = 0
> Why wont the function allow me to pass a 'null' parm?
You're running into a longstanding shortcoming of the function-call
interface inside Postgres: there's only one NULL-parameter flag passed
to a function, so if any of the parameters are NULL then they all are
taken to be NULL. I'm not quite sure why you're seeing that particular
error message, but in any case you'd have gotten a failure from the NOT
NULL constraints on the other two columns.
The only comfort I have to offer is that this will work in 7.1 ---
in fact, your example does work in current development sources.
That's a result of a major rewrite of the function manager. There's
no chance of backpatching the fix into 7.0.* or earlier.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-06-17 05:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-17 05:15 Null parm to a function Gordon Clarke <gordonc@acenet.net.au>
2000-06-17 05:46 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox