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;
;
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? |