Thread: tablename as attribute in pgplsql

tablename as attribute in pgplsql

From

Benoît Bournon

Date:

22 May 2003, 22:58:10

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

Re: tablename as attribute in pgplsql

From

Rod Taylor

Date:

23 May 2003, 02:34:10

> I want to use client table as a variable

In plpgsql you can do that by using EXECUTE

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql-control-structures.html

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: tablename as attribute in pgplsql

From

Stephan Szabo

Date:

23 May 2003, 04:16:13

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.

Re: tablename as attribute in pgplsql

From

Benoît Bournon

Date:

24 May 2003, 21:15:24

It is not possible tu use a specific argument ? type table or tablename ?<br /><br /> execute is not runnig <br /><br
/>Stephan Szabo a écrit:<br /><blockquote cite="mid20030522184108.C51260-100000@megazone23.bigpanda.com"
type="cite"><prewrap="">On Thu, 22 May 2003, [ISO-8859-1] Beno?t Bournon wrote:
 
 </pre><blockquote type="cite"><pre wrap="">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   </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>

Re: tablename as attribute in pgplsql

From

Stephan Szabo

Date:

25 May 2003, 15:07:29

On Fri, 23 May 2003, [ISO-8859-1] Beno�t Bournon wrote:

> It is not possible tu use a specific argument ? type table or tablename ?

Not for the table name in a direct statement like
select foo from variable;

> execute is not runnig


Define not running.


> 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
> >
> >
> >
>

Re: tablename as attribute in pgplsql

From

Benoît Bournon

Date:

01 June 2003, 02:56:11

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>