Thread: How to hand over array as variable in plpgsql function?

Dear list,
I am trying to hand over an float4 array of a previous query in my plpgsql function. The query returns the correct
arraybut when I try to hand over this array to another function I get the error message:
 

ERROR: operator is not unique: "unknown" || real[]
SQL state: 42725
Hint: Could not choose a best candidate operator. You may need to add explicit type casts.
Context: PL/pgSQL function "get_matching_ids_sql_id" line 19 at execute statement. The function looks like this:

DECLARE
id_result integer;
b float4[];BEGIN
EXECUTE 'SELECT precip_control FROM precip_arrays WHERE  id = 400' INTO b;
[...]
EXECUTE 'SELECT id FROM precip_arrays WHERE  '||b||' = precip_control;' INTO id_result;
RETURN  id_result ;
END;

I know that there is a problem with the " '||b||' " part, so could someone point me in the right direction how to right
thisstatement correctly, so that the array can be processed by this SQL statement?
 

Thanks in advance and kind regards

Jan Peters-Anders

-- 
"Feel free" - 5 GB Mailbox, 50 FreeSMS/Monat ...
Jetzt GMX ProMail testen: http://www.gmx.net/de/go/promail

"Jan Peters" <petersjan@gmx.at> writes:
> ERROR: operator is not unique: "unknown" || real[]

> EXECUTE 'SELECT id FROM precip_arrays WHERE  '||b||' = precip_control;' INTO id_result;

Seems like using EXECUTE is the hardest possible way to do this.  Why
don't you just SELECT?

SELECT id FROM precip_arrays WHERE b = precip_control INTO id_result;

If you insist on using EXECUTE then you're going to have to fool with
converting the array to an appropriate text representation.
        regards, tom lane

Hi Tom (and hello all again),
thanks for your reply. 

> Seems like using EXECUTE is the hardest possible way to do this.  Why
> don't you just SELECT?
> 
> SELECT id FROM precip_arrays WHERE b = precip_control INTO id_result;

How should the code look like to do this? I am using EXECUTE because I don't know how to write the SQL equivalent. How
wouldthe SQL code look like if I have these two statements (one returning an array, the other taking this array and
comparesit to some other arrays in the db):
 

SELECT precip_control FROM precip_arrays WHERE  id = 400; --returns an array
SELECT id FROM precip_arrays WHERE [THE RETURNED ARRAY] = precip_scenario;--compares the above array to the arrays in
colum"precip_scenario". 
 

This obviously does NOT work:

SELECT id FROM precip_arrays WHERE (SELECT precip_control FROM precip_arrays WHERE  id = 400;) = precip_control;

Is something like the above statement possible?

Sorry for my ignorance and thanks in advance again

Jan



-- 









Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

"Jan Peters" <petersjan@gmx.at> writes:
>> Seems like using EXECUTE is the hardest possible way to do this.  Why
>> don't you just SELECT?
>> 
>> SELECT id FROM precip_arrays WHERE b = precip_control INTO id_result;

> How should the code look like to do this?

Uh ... just like that.

> SELECT precip_control FROM precip_arrays WHERE  id = 400; --returns an array
> SELECT id FROM precip_arrays WHERE [THE RETURNED ARRAY] = precip_scenario;--compares the above array to the arrays in
colum"precip_scenario". 
 

Sure, SELECT precip_control INTO some_local_array_variable FROM ...
and then use the variable in the next command.

> This obviously does NOT work:

> SELECT id FROM precip_arrays WHERE (SELECT precip_control FROM precip_arrays WHERE  id = 400;) = precip_control;

Well, it would if you got rid of the first semicolon, although most
people would prefer to write this as a join.
        regards, tom lane