X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5F1065358C for ; Sat, 7 May 2005 12:08:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44099-03 for ; Sat, 7 May 2005 15:08:40 +0000 (GMT) Received: from hotmail.com (bay10-f31.bay10.hotmail.com [64.4.37.31]) by svr1.postgresql.org (Postfix) with ESMTP id 5AC8D5359C for ; Sat, 7 May 2005 12:08:39 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Sat, 7 May 2005 08:08:38 -0700 Message-ID: Received: from 62.178.117.119 by by10fd.bay10.hotmail.msn.com with HTTP; Sat, 07 May 2005 15:08:38 GMT X-Originating-IP: [62.178.117.119] X-Originating-Email: [seppwimmer@hotmail.com] X-Sender: seppwimmer@hotmail.com In-Reply-To: <172293c304a9b54a333897da51566c39@mail.nih.gov> From: "Robert Wimmer" To: sdavis2@mail.nih.gov Cc: pgsql-interfaces@postgresql.org Subject: Re: dynamic functions Date: Sat, 07 May 2005 15:08:38 +0000 Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed X-OriginalArrivalTime: 07 May 2005 15:08:38.0731 (UTC) FILETIME=[A5E129B0:01C55316] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.154 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, MSGID_FROM_MTA_HEADER X-Spam-Level: * X-Archive-Number: 200505/25 X-Sequence-Number: 5528 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 >To: "Robert Wimmer" >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/