Re: don't know how to get SELECT

From: Uros Gruber <uros(at)sir-mag(dot)com>
To: Darren Ferguson <darren(at)crystalballinc(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: don't know how to get SELECT
Date: 2002-05-13 18:20:32
Message-ID: 137472094.20020513202032@sir-mag.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Thanks a lot, but I already done it. Also i need SELECT query
and not UPDATE. I know how to deal with UPDATE, but with
select you have to use FOR IN EXECUTE query
Something like this

FOR record | row IN EXECUTE text_expression LOOP
statements
END LOOP;

Only that way you can use SELECT for now

From docs: "The results from SELECT queries are discarded by
EXECUTE, and SELECT INTO is not currently supported within EXECUTE.
So, the only way to extract a result from a dynamically-created
SELECT is to use the FOR-IN-EXECUTE form described later."

--
bye,
Uros

Monday, May 13, 2002, 7:51:17 PM, you wrote:

DF> Use the EXECUTE command.

DF> This allows the execution of dynamic queries

DF> EXECUTE ''UPDATE ''||table||'' SET id = ''||id||''WHERE something =
DF> something'';

DF> This is just an example but it shows how to execute dynamic queries

DF> HTH

DF> Darren Ferguson

DF> On Mon, 13 May 2002, Uros Gruber wrote:

>> Hi!
>>
>> i have some problems with plpgsql. Here is my function
>>
>>
>> create or replace function rm_cat(varchar,int) returns boolean as '
>> declare
>> table alias for $1;
>> id alias for $2;
>> data RECORD;
>> begin
>> ******************************************************************
>> SELECT INTO data lft,rgt FROM d_categories WHERE id_category=id;
>> ******************************************************************
>> IF NOT FOUND THEN
>> RAISE EXCEPTION ''id % does not exist'',id;
>> return 0;
>> end if;
>>
>> -- deleting the leftmost node does not lower lft for all
>> execute ''update '' || quote_ident(table) || '' set level=level-1 where lft > '' || data.lft || ''and rgt < '' || data.rgt || '';'';
>> execute ''update '' || quote_ident(table) || '' set lft=lft-1 where lft >= '' || data.lft || '';'';
>> execute ''update '' || quote_ident(table) || '' set lft=lft-1 where lft >= '' || data.rgt || '';'';
>> execute ''update '' || quote_ident(table) || '' set rgt=rgt-1 where rgt >= '' || data.lft || '';'';
>> execute ''update '' || quote_ident(table) || '' set rgt=rgt-1 where rgt >= '' || data.rgt || '';'';
>> execute ''delete from '' || quote_ident(table) || '' where id_category = '' || id || '';'';
>> return 1;
>> end;
>> 'language 'plpgsql';
>>
>>
>> My problems is in line wraped with ***. What i want is that
>> SELECT have to be dinamyc because i want with an argument to
>> saj for what table do this select. I tried many ways and i
>> think i don't understand something. Can somebody help me
>> solve this.
>>
>>
>>
>> --
>> bye,
>> Uros mailto:uros(dot)gruber(at)sir-mag(dot)com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Uros Gruber 2002-05-13 18:23:29 Re: Can This be done
Previous Message Darko Prenosil 2002-05-13 18:12:53 Re: if exists?