|
Home > Archive > PostgreSQL Discussion > January 2006 > user defined function
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
user defined function
|
|
| Yl Zhou 2006-01-24, 9:23 am |
| 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
| |
| Tom Lane 2006-01-24, 11:23 am |
| 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
| |
|
|
| Richard Huxton 2006-01-24, 11:23 am |
| Tom Lane wrote:
> Yl Zhou <andrew.ylzhou@gmail.com> writes:
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tom Lane 2006-01-24, 11:23 am |
| Richard Huxton <dev@archonet.com> writes:
> Tom Lane wrote:
[color=darkred]
> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Yl Zhou 2006-01-24, 11:23 am |
| 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_FUNCTI
ON_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:
>
>
> 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
>
| |
| Tom Lane 2006-01-24, 11:23 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Yl Zhou 2006-01-24, 11:23 am |
| 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:
> 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
>
| |
| Thomas Hallgren 2006-01-24, 1:23 pm |
| 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:
>
> 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
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Yl Zhou 2006-01-24, 8:24 pm |
| **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:
> 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
>
| |
| Tom Lane 2006-01-24, 8:24 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Scott Marlowe 2006-01-24, 8:24 pm |
| On Tue, 2006-01-24 at 14:38, Tom Lane wrote:
> Yl Zhou <andrew.ylzhou@gmail.com> writes:
>
> 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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| andrew 2006-01-26, 4:56 pm |
| 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@g2switchwor
ks.com> wrote:
> On Tue, 2006-01-24 at 14:38, Tom Lane wrote:
>
> 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.
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2006-01-26, 4:56 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| andrew 2006-01-26, 4:56 pm |
| 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:
>
> 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
>
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Tom Lane 2006-01-26, 4:56 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| andrew 2006-01-26, 4:56 pm |
| 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:
>
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| andrew 2006-01-26, 4:56 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Tom Lane 2006-01-26, 4:57 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| andrew 2006-01-26, 4:57 pm |
| 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:
>
>
>
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
|
|
|
|