Thread: 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