Re: [SQL] tablename as attribute in pgplsql

[Benoît Bournon]

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