Обсуждение: user defined function
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.
andrew
andrew
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
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 */
}
/*
* 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 */
}
On 1/24/06, Tom Lane <tgl@sss.pgh.pa.us > wrote:
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
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?
On 1/24/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
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: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Can anyone tell me whether 7.3 supports unspecified record types or not?
On 1/24/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
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. On 1/24/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote: > 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. >
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? On 1/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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 >
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