Re: tablename as attribute in pgplsql - Mailing list pgsql-sql

Thx to Staphan<br /><br /> Finally I find a solution :<br /><br /> I have tables with different languages and I must
findthe correct languages :<br /><br /> DECLARE<br />  chaine VARCHAR := 'category';<br />  <br />  col_return VARCHAR
:='id_category_unique' ;<br />  col_id VARCHAR := 'id_category' ;<br />  col_value INTEGER := 5 ;<br />  lang_id
INTEGER:= 3 ;<br />  <br />  r RECORD;<br />  str BIGINT;<br /> BEGIN<br />  FOR r in execute 'SELECT ' || col_return
||'AS info<br />        FROM ' || chaine || '<br />        WHERE ' || col_id || ' = ' || col_value || '<br />       
ANDid_language = ' || lang_id<br />  loop<br />   str := r.info;<br />  END LOOP;<br />  RETURN str;<br /> END;<br
/><br/> Stephan Szabo a écrit:<br /><blockquote cite="mid20030527082842.F2773-100000@megazone23.bigpanda.com"
type="cite"><prewrap="">On Tue, 27 May 2003, [ISO-8859-1] Beno?t Bournon wrote:
 
 </pre><blockquote type="cite"><pre wrap="">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 ;   </pre></blockquote><pre wrap="">
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

'DECLAREchaine VARCHAR := 'client';r RECORD;str BIGINT;
BEGINFOR r in execute ''SELECT id_user FROM '' || chaine || '' WHERE id_user
= 112'' loop str := r.id_user;END LOOP;RETURN str;
END;'
 </pre><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><pre wrap="">Stephan Szabo a ?crit:


       </pre><blockquote type="cite"><pre wrap="">On Thu, 22 May 2003, [ISO-8859-1] Beno?t Bournon wrote:




         </pre><blockquote type="cite"><pre wrap="">DECLAREusertablename 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



           </pre></blockquote><pre wrap="">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?

<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html</a>




         </pre></blockquote></blockquote><pre wrap="">
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html</a>


     </pre></blockquote></blockquote><pre wrap="">
 </pre></blockquote>