Re: [GENERAL] user defined function
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 <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> 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 ---(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
Re: [GENERAL] user defined function
andrew <[EMAIL PROTECTED]> 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
Re: [GENERAL] user defined function
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote: > > andrew <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] user defined function
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 <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] user defined function
andrew <[EMAIL PROTECTED]> 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
Re: [GENERAL] user defined function
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 <[EMAIL PROTECTED]> wrote: > andrew <[EMAIL PROTECTED]> 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] user defined function
andrew <[EMAIL PROTECTED]> 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
Re: [GENERAL] user defined function
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 <[EMAIL PROTECTED]> wrote: > On Tue, 2006-01-24 at 14:38, Tom Lane wrote: > > Yl Zhou <[EMAIL PROTECTED]> 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. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] user defined function
On Tue, 2006-01-24 at 14:38, Tom Lane wrote: > Yl Zhou <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] user defined function
Yl Zhou <[EMAIL PROTECTED]> 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
Re: [GENERAL] user defined function
Can anyone tell me whether 7.3 supports unspecified record types or not? On 1/24/06, Tom Lane <[EMAIL PROTECTED]> wrote: Yl Zhou <[EMAIL PROTECTED]> 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 muchof anything useful with unspecified-type records. 8.0 has most of thatfunctionality but I think 8.1 added some things.regards, tom lane
Re: [GENERAL] user defined function
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 <[EMAIL PROTECTED]> 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 ---(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
Re: [GENERAL] user defined function
But I have to use 7.3 due to some limitations. Can I do it in 7.3? On 1/24/06, Tom Lane <[EMAIL PROTECTED]> wrote: Yl Zhou <[EMAIL PROTECTED]> 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 muchof anything useful with unspecified-type records. 8.0 has most of thatfunctionality but I think 8.1 added some things.regards, tom lane
Re: [GENERAL] user defined function
Yl Zhou <[EMAIL PROTECTED]> 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
Re: [GENERAL] user defined function
Do you mean this function? Seems I cannot get much information from it.../* * record_out - output routine for pseudo-type RECORD. */Datumrecord_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 <[EMAIL PROTECTED]
> wrote:Richard Huxton 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 typeswhich is not what you want here. (You could probably still do it witha 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() tojust count nulls should get you there.regards, tom lane
Re: [GENERAL] user defined function
Richard Huxton 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] user defined function
Tom Lane wrote: Yl Zhou <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] user defined function
check http://archives.postgresql.org/pgsql-novice/2005-02/msg00227.php ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] user defined function
Yl Zhou <[EMAIL PROTECTED]> 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
[GENERAL] 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
Re: [GENERAL] User defined function
Hi Moray,
Try the following:
create function test(date)
returns date
as '
select (text_datetime(\'01/12/\' || float8_text(date_part(\'year\', $1) -
1)))::date
as answer;
' language'sql';
Test: (my DATESTYLE is set to European)
select test('30/01/1972'::date);
result -> '01-12-1971'
-Message d'origine-
De : Moray McConnachie <[EMAIL PROTECTED]>
À : [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date : samedi 16 octobre 1999 01:06
Objet : [GENERAL] User defined function
>I need to write a function to return the first of december of the year
>previous to the current year. Part of my problem is I'm not sure how
>to escape quotes in function definitions. I am looking at something
>along the lines of
>
>CREATE FUNCTION startofregyear(date) RETURNS date AS '
>SELECT text_datetime('01/12/' ¦¦ float8_text(datepart('year',$1)-1))
>AS answer;
>' LANGUAGE 'SQL';
>
>but clearly this won't work because of the large number of single
>quotes. I've tried escaping the quotes in the SELECT line with
>backslashes, but that doesn't seem to do it.
>
>Can anyone tell me if this function should work, once I sort out the
>escaping?
>
>Thanks,
>Moray
>
>--
>
>[EMAIL PROTECTED]
>
>
>
>
Re: [GENERAL] User defined function
hi...
>
> > CREATE FUNCTION startofregyear(date) RETURNS date AS '
> > SELECT text_datetime(''01/12/''
> float8_text(datepart(''year'',$1)-1))
> > AS answer;
> > ' LANGUAGE 'SQL';
>
> absolutely not, I tried that already.
> I get "attribute not found" error wherever the opening double-quotes
> are during the create of the function.
try this (it works quite nicely):
select (''12-1-'' || date_part(''year'',$1) - 1)::date;
your problem is 2 fold. first, you need double quotes. second, it isn't
datepart, its date_part. that's the "attribute not found" error.
as a suggestion, try doing the select statement from the command line outside
of a function first to make sure it works. i.e:
select ('12-1-' || date_part('year','now'::date) - 1)::date;
once you know that your select works, pop it into the function. functions don't
return the most helpful error messages =) the command line in psql is much
better for debugging selects/inserts/etc/etc...
--
Aaron J. Seigo
Sys Admin
[GENERAL] User defined function weirdness
Hi
On postgresql 6.4.2 running on digital unix 4.0d. This bit of code
- pfunc.c---
/*
cc -c -I/usr/local/pgsql/include pfunc.c
ld -shared -expect_unresolved '*' -o pfunc.so pfunc.o
cc -o pfunc -I/usr/local/pgsql/include pfunc.c
create function int10() returns int4 as '/path/pfunc.so' LANGUAGE 'c';
create function float10() returns float4 as '/path/pfunc.so' LANGUAGE 'c';
select int10(),10::int4,float10(),10::float4;
*/
#include
#include "postgres.h" /* for char16, etc. */
int4 int10() {
return (int4) 10;
}
float4 float10() {
return (float4) 10.0;
}
does
dbase=> select int10(),10,float10(),10.0;
int10|?column?|float10|?column?
-++---+
10| 10|2.27828| 10
(1 row)
dbase=> select int10(),10,float10(),10.0;
int10|?column?|float10|?column?
-++---+
10| 10|2.27812| 10
(1 row)
Note that float10() gives different answers at different times.
--
+-+--+
|_ ^ _| Dr. Rodney McDuff|
| |\ /|\ /| | Network Development, ITS |
| \ | / | The University of Queensland |
| \ | / | St. Lucia, Brisbane |
| \|/ | Queensland, Australia. 4072. |
|<---+--->| TELEPHONE: +61 7 3365 8220 |
| /|\ | FACSIMILE: +61 7 3365 4477 |
| / | \ | EMAIL: [EMAIL PROTECTED] |
| / | \ | |
| |/ \|/ \| |Ex ignorantia ad sapientiam |
|- v -|Ex luce ad tenebras |
+-+--+
