Re: tablename as attribute in pgplsql

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