From: Benoît Bournon <benoit.bournon@adelis.com>
To: pgsql-sql@postgresql.org
Subject: Re: tablename as attribute in pgplsql
Date: Wed, 28 May 2003 09:58:15 +0200
Message-ID: <3ED46C17.3060705@adelis.com> (raw)
In-Reply-To: <20030527082842.F2773-100000@megazone23.bigpanda.com>
References: <20030527082842.F2773-100000@megazone23.bigpanda.com>
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
>>>
>>>
>>>
>>>
>>>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: benoit.bournon@adelis.com
Subject: Re: tablename as attribute in pgplsql
In-Reply-To: <3ED46C17.3060705@adelis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox