From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Cristian Prieto <cristian(at)clickdiario(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with case in select |
Date: | 2005-04-01 07:09:05 |
Message-ID: | 424CF391.5020803@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Cristian Prieto wrote:
> Hello, I have the following sp, I need to return a 'Flag' if the ID of
> the row is in the prior select, I tryed with the following code:
>
> create or replace function sp_getadvertisers(ag integer) returns record as
> $main$
> declare
> alladv record;
> retrec record;
> begin
> -- Primero buscamos todas las campanas de este advertiser q sean hijos
> select into alladv userid from advertiser where agenciaid=ag and
> adv_parent is not null;
> select into retrec userid, case when userid in alladv then 'P' when
> adv_parent is not null then 'S' else null end from advertiser where
> agenciaid=ag;
> return retrec;
> end;
> $main$
> language plpgsql;
>
> But it returns me:
> ERROR: syntax error at or near "$1" at character 38
> QUERY: SELECT userid, case when userid in $1 then 'P' when
> adv_parent is not null then 'S' else null end from advertiser where
> agenciaid= $2
> CONTEXT: PL/pgSQL function "sp_getadvertisers" line 7 at select into
> variables
> LINE 1: SELECT userid, case when userid in $1 then 'P' when adv_p...
>
> Any Idea how could I change it to work? ideas???
Some things to check:
1. Do you have a column "ag" in either of those tables?
2. You should make "alladv" an integer (or whatever type "userid" is)
rather than a record.
3. "userid in alladv" - why not "userid = alladv"?
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-04-01 08:01:18 | Re: not able to connect to Database |
Previous Message | Srikanth Utpala (Virinchi) | 2005-04-01 06:42:43 | getting information of tables and indexes |