Re: Selects in Function Return Wrong Value

Nick Thuesen <jimmy(dot)mcshaw(at)gmail(dot)com> writes:
> CREATE OR REPLACE FUNCTION "public"."event_select_on_id" (event_id
^^^^^^^^
> integer) RETURNS "public"."event" AS
> $body$
> DECLARE
> return_event public.event;
> BEGIN
> SELECT * INTO return_event FROM event WHERE event_id = $1;
^^^^^^^^
> RETURN return_event;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

plpgsql is probably seeing this as "WHERE $1 = $1", ie, true at every
row of the table. It's a bad idea to name local variables or parameters
the same as table or field names that you need to reference in the
function, because plpgsql is very likely to misinterpret the field
references as variable references.

regards, tom lane