X-Original-To: pgsql-sql@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 3C41A924E82 for ; Wed, 28 May 2003 03:58:27 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 44266-07 for ; Wed, 28 May 2003 03:58:22 -0400 (EDT) Received: from neptune.adelis.net (neptune.adelis.net [195.115.171.102]) by developer.postgresql.org (Postfix) with ESMTP id D8EF0924E4F for ; Wed, 28 May 2003 03:58:21 -0400 (EDT) Received: from adelis.com (ben.adelis.net [195.115.171.16]) by neptune.adelis.net (Postfix) with ESMTP id 26BCC791BC for ; Wed, 28 May 2003 09:50:57 +0200 (CEST) Message-ID: <3ED46C17.3060705@adelis.com> Date: Wed, 28 May 2003 09:58:15 +0200 From: =?ISO-8859-1?Q?Beno=EEt_Bournon?= User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr-FR; rv:1.2.1) Gecko/20030225 X-Accept-Language: fr, en-us, en, ja MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: tablename as attribute in pgplsql References: <20030527082842.F2773-100000@megazone23.bigpanda.com> In-Reply-To: <20030527082842.F2773-100000@megazone23.bigpanda.com> Content-Type: multipart/alternative; boundary="------------020406050608000305050807" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/589 X-Sequence-Number: 13754 --------------020406050608000305050807 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Thx to Staphan Finally I find a solution : I have tables with different languages and I must find the correct languages : DECLARE chaine VARCHAR := 'category'; col_return VARCHAR := 'id_category_unique' ; col_id VARCHAR := 'id_category' ; col_value INTEGER := 5 ; lang_id INTEGER := 3 ; r RECORD; str BIGINT; BEGIN FOR r in execute 'SELECT ' || col_return ||' AS info FROM ' || chaine || ' WHERE ' || col_id || ' = ' || col_value || ' AND id_language = ' || lang_id loop str := r.info; END LOOP; RETURN str; END; Stephan Szabo a écrit: >On Tue, 27 May 2003, [ISO-8859-1] Beno?t Bournon wrote: > > > >>I try to do that : >> >>DECLARE >> chaine VARCHAR := 'client' ; >> str BIGINT ; >>BEGIN >> >> SELECT id_user into str FROM client WHERE id_user = 112 ; >> >> EXECUTE 'SELECT id_user into str FROM chaine WHERE id_user = 112' ; >> >> RETURN str ; >>END ; >> >> > >It would be (doubling quotes because the function body is in quotes) >EXECUTE ''SELECT id_user into str FROM '' || chaine || '' WHERE id_user = >112'' > >except that IIRC you can't do select into str from execute, instead (as I >alluded to below) I think you need to say something wacky like > >'DECLARE > chaine VARCHAR := 'client'; > r RECORD; > str BIGINT; >BEGIN > FOR r in execute ''SELECT id_user FROM '' || chaine || '' WHERE id_user >= 112'' loop > str := r.id_user; > END LOOP; > RETURN str; >END;' > > > >>>>Stephan Szabo a ?crit: >>>> >>>> >>>> >>>> >>>> >>>>>On Thu, 22 May 2003, [ISO-8859-1] Beno?t Bournon wrote: >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>>>DECLARE >>>>>> usertablename ALIAS FOR $1 ; >>>>>> userid ALIAS FOR $2 ; >>>>>> >>>>>> id_lang BIGINT ; >>>>>> >>>>>> var VARCHAR(200) ; >>>>>> >>>>>>BEGIN >>>>>> var := 'client' ; >>>>>> >>>>>> SELECT id_language INTO id_lang FROM client WHERE id_user = userid ; >>>>>> IF NOT (id_lang = 1 OR id_lang = 3) THEN >>>>>> id_lang = 1 ; >>>>>> END IF ; >>>>>> >>>>>> RETURN id_lang ; >>>>>> >>>>>>END ; >>>>>> >>>>>>I want to use client table as a variable >>>>>> >>>>>>How could I do >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>Look at the documentation for EXECUTE. Unfortunately using EXECUTE into a >>>>>variable is a little complicated right now AFAIR because you can't using >>>>>select ... INTO variable inside the execute, but instead need to use >>>>>something like for in execute loop. >>>>> >>>>> >>>>> >>>>>---------------------------(end of broadcast)--------------------------- >>>>>TIP 5: Have you checked our extensive FAQ? >>>>> >>>>>http://www.postgresql.org/docs/faqs/FAQ.html >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/docs/faqs/FAQ.html >>> >>> >>> >>> >>> > > > > --------------020406050608000305050807 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Thx to Staphan

Finally I find a solution :

I have tables with different languages and I must find the correct languages :

DECLARE
 chaine VARCHAR := 'category';
 
 col_return VARCHAR := 'id_category_unique' ;
 col_id VARCHAR := 'id_category' ;
 col_value INTEGER := 5 ;
 lang_id INTEGER := 3 ;
 
 r RECORD;
 str BIGINT;
BEGIN
 FOR r in execute 'SELECT ' || col_return ||' AS info
       FROM ' || chaine || '
       WHERE ' || col_id || ' = ' || col_value || '
       AND id_language = ' || lang_id
 loop
  str := r.info;
 END LOOP;
 RETURN str;
END;

Stephan Szabo a écrit:
On Tue, 27 May 2003, [ISO-8859-1] Beno?t Bournon wrote:

  
I try to do that :

DECLARE
       chaine VARCHAR := 'client' ;
       str BIGINT ;
BEGIN

     SELECT id_user into str FROM client WHERE id_user = 112 ;

     EXECUTE 'SELECT id_user into str FROM chaine WHERE id_user = 112' ;

     RETURN str ;
END ;
    

It would be (doubling quotes because the function body is in quotes)
EXECUTE ''SELECT id_user into str FROM '' || chaine || '' WHERE id_user =
112''

except that IIRC you can't do select into str from execute, instead (as I
alluded to below) I think you need to say something wacky like

'DECLARE
 chaine VARCHAR := 'client';
 r RECORD;
 str BIGINT;
BEGIN
 FOR r in execute ''SELECT id_user FROM '' || chaine || '' WHERE id_user
= 112'' loop
  str := r.id_user;
 END LOOP;
 RETURN str;
END;'

  
Stephan Szabo a ?crit:



        
On Thu, 22 May 2003, [ISO-8859-1] Beno?t Bournon wrote:





          
DECLARE
 usertablename ALIAS FOR $1 ;
 userid ALIAS FOR $2 ;

 id_lang BIGINT ;

 var VARCHAR(200) ;

BEGIN
    var := 'client' ;

    SELECT id_language INTO id_lang FROM client WHERE id_user = userid ;
    IF NOT (id_lang = 1 OR id_lang = 3) THEN
        id_lang = 1 ;
    END IF ;

    RETURN id_lang ;

END ;

I want to use client table as a variable

How could I do




            
Look at the documentation for EXECUTE.  Unfortunately using EXECUTE into a
variable is a little complicated right now AFAIR because you can't using
select ... INTO variable inside the execute, but instead need to use
something like for in execute loop.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html





          
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html
--------------020406050608000305050807--