pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feeddynamic functions
3+ messages / 2 participants
[nested] [flat]
* dynamic functions
@ 2005-05-05 21:49 Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Wimmer @ 2005-05-05 21:49 UTC (permalink / raw)
To: pgsql-interfaces
hi ,
is there any way to build "dynamic functions" in plpgsql ?
my problem is, i have a table defining attributes like
CREATE TABLE attribute.attribute (
name NAME PRIMARY KEY,
descr VARCHAR(256),
regex VARCHAR(50) NOT NULL,
minlen INTEGER NOT NULL DEFAULT 0,
maxlen INTEGER NOT NULL DEFAULT 64
-- validate NAME // not implemented
);
and a function like
CREATE OR REPLACE FUNCTION
attribute.match(TEXT,TEXT,BOOLEAN) RETURNS INTEGER AS '
.. check the value (against the regex etc) ..
END; ' LANGUAGE plpgsql
in some cases i would like to check the values also against a function for
example
CREATE FUNCTION attribute.check_range(TEXT) RETURNS INTEGER AS '
BEGIN
IF $1 ... THEN RETURN -1; END IF;
RETURN 0;
END; '
....
so that i can do the following in my match function
CREATE OR REPLACE FUNCTION
attribute.match(TEXT,TEXT,BOOLEAN) RETURNS INTEGER AS '
.. check the value (against the regex etc) ..
IF attrib.validate NOT IS NULL THEN
cmd := atrib.validate || ''('' $1 '')'' ||;
-- ??
-- EXECUTE ''SELECT '' || cmd; // SELECT does not work in EXECUTE
-- ??
END IF
END; ' LANGUAGE plpgsql
is there a way to get a result back from EXECUTE ?
i hope you can understand my description of the problem
i am using postgresQL 7.4 on debian
thanx
sepp
_________________________________________________________________
Recherchieren Sie auf eigene Faust mit der wohl besten Suchmaschine im Netz.
http://search.msn.at/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: dynamic functions
@ 2005-05-05 22:30 Sean Davis <sdavis2@mail.nih.gov>
parent: Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sean Davis @ 2005-05-05 22:30 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: pgsql-interfaces
From the manual, you can use FOR-IN-EXECUTE or a cursor for pl/pgsql:
The results from SELECT commands are discarded by EXECUTE, and SELECT
INTO is not currently supported within EXECUTE. So there is no way to
extract a result from a dynamically-created SELECT using the plain
EXECUTE command. There are two other ways to do it, however: one is to
use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the
other is to use a cursor with OPEN-FOR-EXECUTE, as described in Section
35.8.2.
Does this help any? If not, they you could use another procedure
language like pl/perl or others. With many of them, you can build the
SQL query, then execute it and get the returned results. Again, see
the manual section for pl/perl (for example,
http://www.postgresql.org/docs/8.0/static/plperl-database.html) and
others.
Sean
On May 5, 2005, at 5:49 PM, Robert Wimmer wrote:
> hi ,
>
> is there any way to build "dynamic functions" in plpgsql ?
>
> my problem is, i have a table defining attributes like
>
> CREATE TABLE attribute.attribute (
> name NAME PRIMARY KEY,
> descr VARCHAR(256),
> regex VARCHAR(50) NOT NULL,
> minlen INTEGER NOT NULL DEFAULT 0,
> maxlen INTEGER NOT NULL DEFAULT 64
> -- validate NAME // not implemented
> );
>
> and a function like
>
> CREATE OR REPLACE FUNCTION
> attribute.match(TEXT,TEXT,BOOLEAN) RETURNS INTEGER AS '
>
> .. check the value (against the regex etc) ..
>
> END; ' LANGUAGE plpgsql
>
> in some cases i would like to check the values also against a function
> for example
>
> CREATE FUNCTION attribute.check_range(TEXT) RETURNS INTEGER AS '
> BEGIN
> IF $1 ... THEN RETURN -1; END IF;
> RETURN 0;
> END; '
> ....
>
> so that i can do the following in my match function
>
> CREATE OR REPLACE FUNCTION
> attribute.match(TEXT,TEXT,BOOLEAN) RETURNS INTEGER AS '
>
> .. check the value (against the regex etc) ..
> IF attrib.validate NOT IS NULL THEN
> cmd := atrib.validate || ''('' $1 '')'' ||;
> -- ??
> -- EXECUTE ''SELECT '' || cmd; // SELECT does not work in EXECUTE
> -- ??
> END IF
>
> END; ' LANGUAGE plpgsql
>
> is there a way to get a result back from EXECUTE ?
>
> i hope you can understand my description of the problem
> i am using postgresQL 7.4 on debian
>
> thanx
>
> sepp
>
> _________________________________________________________________
> Recherchieren Sie auf eigene Faust mit der wohl besten Suchmaschine im
> Netz. http://search.msn.at/
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: dynamic functions
@ 2005-05-07 15:08 Robert Wimmer <seppwimmer@hotmail.com>
parent: Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Robert Wimmer @ 2005-05-07 15:08 UTC (permalink / raw)
To: sdavis2@mail.nih.gov; +Cc: pgsql-interfaces
hi sean,
i tried the FOR-IN-EXECUTE methode before but i couldn't manage it. now i
got it. it's tricky and the code looks strange. the first trick is (you cant
use an integer as a result because in that case pgplsql would expext a
'normal' FOR i IN 2..5 LOOP.) i had to create a dummy type for the result.
CREATE TYPE attribute.temp AS (err INTEGER);
And then I misused an empty FOR LOOP as an assignment ...
###
CREATE OR REPLACE FUNCTION attribute.validate(TEXT,TEXT) RETURNS INTEGER AS
'
DECLARE
tmp RECORD;
cmd TEXT;
BEGIN
cmd := ''SELECT * FROM '' || $1 || ''('' || quote_literal($2) || '')'';
FOR tmp IN EXECUTE cmd LOOP END LOOP;
RETURN tmp.err;
END; '
LANGUAGE plpgsql;
###
but it works ...
thanks for the inspiration
sepp
its a problem that there is no example in the documentation for this
workaround :-<
>From: Sean Davis <sdavis2@mail.nih.gov>
>To: "Robert Wimmer" <seppwimmer@hotmail.com>
>CC: pgsql-interfaces@postgresql.org
>Subject: Re: [INTERFACES] dynamic functions
>Date: Thu, 5 May 2005 18:30:40 -0400
>
From the manual, you can use FOR-IN-EXECUTE or a cursor for pl/pgsql:
>
>The results from SELECT commands are discarded by EXECUTE, and SELECT INTO
>is not currently supported within EXECUTE. So there is no way to extract a
>result from a dynamically-created SELECT using the plain EXECUTE command.
>There are two other ways to do it, however: one is to use the
>FOR-IN-EXECUTE loop form described in Section 35.7.4, and the other is to
>use a cursor with OPEN-FOR-EXECUTE, as described in Section 35.8.2.
>
>Does this help any? If not, they you could use another procedure language
>like pl/perl or others. With many of them, you can build the SQL query,
>then execute it and get the returned results. Again, see the manual
>section for pl/perl (for example,
>http://www.postgresql.org/docs/8.0/static/plperl-database.html) and others.
>
>Sean
>
>
>On May 5, 2005, at 5:49 PM, Robert Wimmer wrote:
>
>>hi ,
>>
>>is there any way to build "dynamic functions" in plpgsql ?
>>
>>my problem is, i have a table defining attributes like
>>
>>CREATE TABLE attribute.attribute (
>> name NAME PRIMARY KEY,
>> descr VARCHAR(256),
>> regex VARCHAR(50) NOT NULL,
>> minlen INTEGER NOT NULL DEFAULT 0,
>> maxlen INTEGER NOT NULL DEFAULT 64
>> -- validate NAME // not implemented
>> );
>>
>>and a function like
>>
>>CREATE OR REPLACE FUNCTION
>>attribute.match(TEXT,TEXT,BOOLEAN) RETURNS INTEGER AS '
>>
>>.. check the value (against the regex etc) ..
>>
>>END; ' LANGUAGE plpgsql
>>
>>in some cases i would like to check the values also against a function for
>>example
>>
>>CREATE FUNCTION attribute.check_range(TEXT) RETURNS INTEGER AS '
>>BEGIN
>> IF $1 ... THEN RETURN -1; END IF;
>> RETURN 0;
>>END; '
>>....
>>
>>so that i can do the following in my match function
>>
>>CREATE OR REPLACE FUNCTION
>>attribute.match(TEXT,TEXT,BOOLEAN) RETURNS INTEGER AS '
>>
>> .. check the value (against the regex etc) ..
>> IF attrib.validate NOT IS NULL THEN
>> cmd := atrib.validate || ''('' $1 '')'' ||;
>> -- ??
>> -- EXECUTE ''SELECT '' || cmd; // SELECT does not work in EXECUTE
>> -- ??
>> END IF
>>
>>END; ' LANGUAGE plpgsql
>>
>>is there a way to get a result back from EXECUTE ?
>>
>>i hope you can understand my description of the problem
>>i am using postgresQL 7.4 on debian
>>
>>thanx
>>
>>sepp
>>
>>_________________________________________________________________
>>Recherchieren Sie auf eigene Faust mit der wohl besten Suchmaschine im
>>Netz. http://search.msn.at/
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
_________________________________________________________________
Recherchieren Sie auf eigene Faust mit der wohl besten Suchmaschine im Netz.
http://search.msn.at/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-07 15:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-05 21:49 dynamic functions Robert Wimmer <seppwimmer@hotmail.com>
2005-05-05 22:30 ` Sean Davis <sdavis2@mail.nih.gov>
2005-05-07 15:08 ` Robert Wimmer <seppwimmer@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox