array_append not working

From: "Lucas F(dot)" <lucasf(at)vagabond-software(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: array_append not working
Date: 2005-04-05 22:24:27
Message-ID: 005301c53a2e$3aea08d0$a001a8c0@sf.vagabond.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry, this is probably a dumb mistake on my part. New to Postgres. Running
8.0 on Linux.

The array_append below is giving me a syntax error, and I have no idea what
I am doing wrong.

Also as a side note is there a straightforward way to get a value from a
dynamic query into a local variable? The loop construct at the end is the
only way I know how...

CREATE OR REPLACE FUNCTION "property"."get_score_type_id_from_url" (p_url
varchar) RETURNS integer AS
$body$
/* returns a score_type_id given a filter url fragment */

declare

v_parsed text[];
v_query_array text[] = '{}';
v_array_index integer = 1;
rec record;
v_value integer;

begin

if (char_length(p_url) = 0 or p_url is null) then
return 1;
end if;

v_parsed := string_to_array(p_url,'/');

FOR rec IN
SELECT * FROM property.filteritem ORDER BY filter_group_id, sort_order
LOOP

if (rec.item_name = v_parsed[v_array_index]) then
v_value = rec.item_field_value;
v_array_index = v_array_index + 1;
else
v_value = 0;
end if;

array_append(v_query_array,(rec.item_field_name || '=' ||
v_value::text)::text);

END LOOP;

/* todo: doesn't need to be a loop here */
FOR rec IN EXECUTE
'SELECT score_type_id FROM array_to_string(v_query) WHERE ' ||
array_to_string(v_parsed,' AND ')
LOOP

return rec.score_type_id;

END LOOP;

end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2005-04-06 00:15:38 Re: array_append not working
Previous Message Joshua D. Drake 2005-04-05 21:49:31 Re: [HACKERS] plPHP in core?