Re: cursors in postgres

2007/3/29, Jasbinder Singh Bali <jsbali(at)gmail(dot)com>:
> Hi,
> I've written a function using cursors as follows:
> can anyone please comment on the text in red.
>
>
> ------------------------------------------------------
>
> CREATE OR REPLACE FUNCTION
> sp_insert_tbl_email_address(int4, text, text, text)
> RETURNS void AS
> $BODY$
> DECLARE
> uid int4 ;
> src text;
> local text;
> domain text;
> cur_dup_check CURSOR FOR SELECT * FROM tbl_email_address
> WHERE unmask_id=$1 and source=$2 and email_local=$3 and email_domain=$4;
>
>
> BEGIN
>
> OPEN cur_dup_check ;
>
> FETCH cur_dup_check INTO uid,src,local,domain;
> --need to check the fetch status of the cursor whether any rows were
> returned or not and keep moving to the next record till fetch status is not
> zero
>
> INSERT INTO
> tbl_email_address(unmask_id,source,email_local,email_domain)
> VALUES ($1,$2,$3,$4) ;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION sp_insert_tbl_email_address(int4, int4,
> text, text, text) OWNER TO postgres;
>

You could check builtin FOUND variable.

Did you read http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html ?

and do you realize that probably, this can (and should) be done
without cursors? or even without any user defined function?

if I understand correctly, you want something like:
INSERT INTO table(a,b,c) SELECT 'a','b','c' WHERE NOT EXISTS
( SELECT * FROM table WHERE (a,b,c) = ('a','b','c') );

--
Filip Rembiałkowski