Thread: Selects in Function Return Wrong Value

I'm glad there is a novice mailing list because I've spent to much
time in MSSQL and trying to figure out postgres is very frustrating.
Here's my prob.

I've created a function that uses the 'event' table.

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;


This was suppose to be a test function so I could get the feeling for
postgres but no matter what value I pass in it always returns the
first item in the table.  This happens even if I pass in an id that
doesn't exists.  I scanned through the archives for a while and can't
figure out what I'm doing wrong.  Thanks in advance for helping a
noob.

Nick Thuesen <jimmy.mcshaw@gmail.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