From: Sean Davis <sdavis2@mail.nih.gov>
To: Robert Wimmer <seppwimmer@hotmail.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: dynamic functions
Date: Thu, 5 May 2005 18:30:40 -0400
Message-ID: <172293c304a9b54a333897da51566c39@mail.nih.gov> (raw)
In-Reply-To: <BAY10-F1223F44B77E5F515A0B5AD01A0@phx.gbl>
References: <BAY10-F1223F44B77E5F515A0B5AD01A0@phx.gbl>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: sdavis2@mail.nih.gov, seppwimmer@hotmail.com
Subject: Re: dynamic functions
In-Reply-To: <172293c304a9b54a333897da51566c39@mail.nih.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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