Thread: array_append not working

array_append not working

From
"Lucas F."
Date:
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;
;


Re: array_append not working

From
Joe Conway
Date:
Lucas F. wrote:
> 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.
>

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

You need to assign the result of array_append() to a variable. It
doesn't change v_query_array in place.

Joe