user defined function

Yl Zhou <andrew.ylzhou@gmail.com> writes:

I want to implement a UDF that can accept a parameter which is a tuple of
any table, and returns the number of NULL attributes in this tuple.
Different tables may have different schemas. How can I implement this
function? Thanks.

You could do that in C, but none of the available PLs support it.

regards, tom lane

check

http://archives.postgresql.org/pgsql-novice/2005-02/msg00227.php

Tom Lane wrote:

Yl Zhou <andrew.ylzhou@gmail.com> writes:

I want to implement a UDF that can accept a parameter which is a tuple of
any table, and returns the number of NULL attributes in this tuple.
Different tables may have different schemas. How can I implement this
function? Thanks.

You could do that in C, but none of the available PLs support it.

How would you define the signature for the function? One parameter of
type anyelement?

--
Richard Huxton
Archonet Ltd

Richard Huxton <dev@archonet.com> writes:

Tom Lane wrote:

You could do that in C, but none of the available PLs support it.

How would you define the signature for the function? One parameter of
type anyelement?

Type RECORD would be a better choice --- ANYELEMENT allows scalar types
which is not what you want here. (You could probably still do it with
a function declared that way, but it'd have to take extra steps to
defend itself against being passed, say, an integer.)

If you're looking for a coding model, stripping down record_out() to
just count nulls should get you there.

regards, tom lane

Do you mean this function? Seems I cannot get much information from it...
/*
* record_out - output routine for pseudo-type RECORD.
*/
Datum
record_out(PG_FUNCTION_ARGS)
{
elog(ERROR, "Cannot display a value of type %s", "RECORD");

PG_RETURN_VOID(); /* keep compiler quiet */
}

Yl Zhou <andrew.ylzhou@gmail.com> writes:

Do you mean this function? Seems I cannot get much information from it...

That would appear to be Postgres 7.3 :-(

You need a considerably newer version of Postgres if you want to do much
of anything useful with unspecified-type records. 8.0 has most of that
functionality but I think 8.1 added some things.

regards, tom lane

But I have to use 7.3 due to some limitations. Can I do it in 7.3?

For what it's worth, the next release of PL/Java has support for both RECORD parameters and
SETOF RECORD return types. The adventurous can try out the current CVS HEAD.

Regards,
Thomas Hallgren

Tom Lane wrote:

**Can anyone tell me whether 7.3 supports unspecified record types or not?
**

Yl Zhou <andrew.ylzhou@gmail.com> writes:

But I have to use 7.3 due to some limitations. Can I do it in 7.3?

Probably, but I forget how (and I can guarantee that it will break
when you do move to 8.0 or later, because we changed the internal
representation of rowtype arguments). You'd be *much* better off to
spend your time fixing whatever it is that's keeping you on 7.3.

regards, tom lane

On Tue, 2006-01-24 at 14:38, Tom Lane wrote:

Yl Zhou <andrew.ylzhou@gmail.com> writes:

But I have to use 7.3 due to some limitations. Can I do it in 7.3?

Probably, but I forget how (and I can guarantee that it will break
when you do move to 8.0 or later, because we changed the internal
representation of rowtype arguments). You'd be *much* better off to
spend your time fixing whatever it is that's keeping you on 7.3.

For some reason I'm remember 7.4 as being the first version that let you
do this. Not for certain. I didn't run 7.3 in production though, so I
might have missed it if it could do this.

I use 7.3 and use RECORD as the input data type of the function by
"create function foo(record) returns int4 as '$libdir/bar' language
C". But I got this error msg:" ERROR: parser: parse error at or near
"record" at character". What is the problem? I look up the 7.3
manual. it seems record is a supported pseudo data type.

andrew <andrew.ylzhou@gmail.com> writes:

I use 7.3 and use RECORD as the input data type of the function by
"create function foo(record) returns int4 as '$libdir/bar' language
C". But I got this error msg:" ERROR: parser: parse error at or near
"record" at character". What is the problem?

Sure you typed it correctly? I get

regression=# create function foo(record) returns int4 as '$libdir/bar' language C;
ERROR: stat failed on file '$libdir/bar': No such file or directory
regression=#

so it's getting past the parse-error stage here.

regards, tom lane

The following is just copied from the screen.
backend> create function foo(record) returns int4 as '$libdir/bar' language C
QUERY: create function foo(record) returns int4 as '$libdir/bar' language C

ERROR: parser: parse error at or near "record" at character 21
in Warn_restart code

What is the problem here? Did you test it on 7.3?

andrew <andrew.ylzhou@gmail.com> writes:

ERROR: parser: parse error at or near "record" at character 21
in Warn_restart code

What is the problem here? Did you test it on 7.3?

Yeah, 7.3.13 to be exact. (There have been a couple of changes in the
parser in the 7.3 branch, according to the CVS logs, but none look to
be related to this.) Where did that "in Warn_restart code" bit come
from? There's no such string anywhere in the 7.3 sources. Perhaps you
are playing with a copy that someone has modified/broken?

regards, tom lane

Sorry, I modified the parser code and forgot abt it. Now there is no
problem in creating the function. But there is another problem. I
create a function to accept record type parameter. But when I call it
on a specific composite type, error is reported. The followings are
what I have done:

backend> create function complete(record) returns int4 as
'$libdir/qualityudf' language C
QUERY: create function complete(record) returns int4 as
'$libdir/qualityudf' language C

backend> select *, complete(Person) from Person
QUERY: select *, complete(Person) from Person

ERROR: Function complete(person) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

On 1/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

andrew <andrew.ylzhou@gmail.com> writes:

ERROR: parser: parse error at or near "record" at character 21
in Warn_restart code

What is the problem here? Did you test it on 7.3?

Yeah, 7.3.13 to be exact. (There have been a couple of changes in the
parser in the 7.3 branch, according to the CVS logs, but none look to
be related to this.) Where did that "in Warn_restart code" bit come
from? There's no such string anywhere in the 7.3 sources. Perhaps you
are playing with a copy that someone has modified/broken?

regards, tom lane

--
andrew

sorry, mistakenly leave out another try:

backend> select *, complete(CAST (Person AS record)) from Person
QUERY: select *, complete(CAST (Person AS record)) from Person

ERROR: Relation reference "person" cannot be used in an expression

On 1/25/06, andrew <andrew.ylzhou@gmail.com> wrote:

Sorry, I modified the parser code and forgot abt it. Now there is no
problem in creating the function. But there is another problem. I
create a function to accept record type parameter. But when I call it
on a specific composite type, error is reported. The followings are
what I have done:

backend> create function complete(record) returns int4 as
'$libdir/qualityudf' language C
QUERY: create function complete(record) returns int4 as
'$libdir/qualityudf' language C

backend> select *, complete(Person) from Person
QUERY: select *, complete(Person) from Person

ERROR: Function complete(person) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

On 1/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

andrew <andrew.ylzhou@gmail.com> writes:

ERROR: parser: parse error at or near "record" at character 21
in Warn_restart code

What is the problem here? Did you test it on 7.3?

Yeah, 7.3.13 to be exact. (There have been a couple of changes in the
parser in the 7.3 branch, according to the CVS logs, but none look to
be related to this.) Where did that "in Warn_restart code" bit come
from? There's no such string anywhere in the 7.3 sources. Perhaps you
are playing with a copy that someone has modified/broken?

regards, tom lane

--
andrew

--
andrew

andrew <andrew.ylzhou@gmail.com> writes:

Sorry, I modified the parser code and forgot abt it. Now there is no
problem in creating the function. But there is another problem. I
create a function to accept record type parameter. But when I call it
on a specific composite type, error is reported. The followings are
what I have done:

backend> create function complete(record) returns int4 as
'$libdir/qualityudf' language C
QUERY: create function complete(record) returns int4 as
'$libdir/qualityudf' language C

backend> select *, complete(Person) from Person
QUERY: select *, complete(Person) from Person

ERROR: Function complete(person) does not exist

Hmm. Looking at parse_coerce.c, 8.1 is the first release that thinks
named composite types can be coerced to RECORD. I think you may be
forced to upgrade if you want this to work. Changing 7.3's coerce_type()
to allow this case would be simple enough, but I think you are still
going to be minus a lot of infrastructure that's required to make it
actually do anything useful :-(

regards, tom lane

Thanks, Tom. It is done by modifying coerce_type() and
can_coerce_type(). The reason I have to keep to verson 7.3 is I am
working on a research prototype that is built over pgsql 7.3. I need
the extra functions provided by that prototype.

On 1/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

andrew <andrew.ylzhou@gmail.com> writes:

Sorry, I modified the parser code and forgot abt it. Now there is no
problem in creating the function. But there is another problem. I
create a function to accept record type parameter. But when I call it
on a specific composite type, error is reported. The followings are
what I have done:

backend> create function complete(record) returns int4 as
'$libdir/qualityudf' language C
QUERY: create function complete(record) returns int4 as
'$libdir/qualityudf' language C

backend> select *, complete(Person) from Person
QUERY: select *, complete(Person) from Person

ERROR: Function complete(person) does not exist

Hmm. Looking at parse_coerce.c, 8.1 is the first release that thinks
named composite types can be coerced to RECORD. I think you may be
forced to upgrade if you want this to work. Changing 7.3's coerce_type()
to allow this case would be simple enough, but I think you are still
going to be minus a lot of infrastructure that's required to make it
actually do anything useful :-(

regards, tom lane

--
andrew