Thread: dynamic functions

dynamic functions

From

"Robert Wimmer"

Date:

05 May 2005, 21:49:37

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/

Re: dynamic functions

From

Sean Davis

Date:

05 May 2005, 22:30:54

 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
>

Re: dynamic functions

From

"Robert Wimmer"

Date:

07 May 2005, 15:08:48

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/