pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtablename as attribute in pgplsql
6+ messages / 3 participants
[nested] [flat]
* tablename as attribute in pgplsql
@ 2003-05-22 15:46 Benoît Bournon <benoit.bournon@adelis.com>
2003-05-23 00:41 ` Re: tablename as attribute in pgplsql Rod Taylor <rbt@rbt.ca>
2003-05-23 01:42 ` Re: tablename as attribute in pgplsql Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 6+ messages in thread
From: Benoît Bournon @ 2003-05-22 15:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tablename as attribute in pgplsql
2003-05-22 15:46 tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
@ 2003-05-23 00:41 ` Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Rod Taylor @ 2003-05-23 00:41 UTC (permalink / raw)
To: Benoît Bournon <benoit.bournon@adelis.com>; +Cc: pgsql-sql
> 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.h...
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tablename as attribute in pgplsql
2003-05-22 15:46 tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
@ 2003-05-23 01:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-23 07:38 ` Re: tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-05-23 01:42 UTC (permalink / raw)
To: Benoît Bournon <benoit.bournon@adelis.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tablename as attribute in pgplsql
2003-05-22 15:46 tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
2003-05-23 01:42 ` Re: tablename as attribute in pgplsql Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-05-23 07:38 ` Benoît Bournon <benoit.bournon@adelis.com>
2003-05-25 16:07 ` Re: tablename as attribute in pgplsql Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Benoît Bournon @ 2003-05-23 07:38 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
It is not possible tu use a specific argument ? type table or tablename ?
execute is not runnig
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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tablename as attribute in pgplsql
2003-05-22 15:46 tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
2003-05-23 01:42 ` Re: tablename as attribute in pgplsql Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-23 07:38 ` Re: tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
@ 2003-05-25 16:07 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-05-25 16:07 UTC (permalink / raw)
To: Benoît Bournon <benoit.bournon@adelis.com>; +Cc: pgsql-sql
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
> >
> >
> >
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tablename as attribute in pgplsql
@ 2003-05-28 07:58 Benoît Bournon <benoit.bournon@adelis.com>
0 siblings, 0 replies; 6+ messages in thread
From: Benoît Bournon @ 2003-05-28 07:58 UTC (permalink / raw)
To: pgsql-sql
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
>>>
>>>
>>>
>>>
>>>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-05-28 07:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-22 15:46 tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
2003-05-23 00:41 ` Rod Taylor <rbt@rbt.ca>
2003-05-23 01:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-23 07:38 ` Benoît Bournon <benoit.bournon@adelis.com>
2003-05-25 16:07 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-28 07:58 Re: tablename as attribute in pgplsql Benoît Bournon <benoit.bournon@adelis.com>
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