Re: cursors in postgres

2007/3/29, Jasbinder Singh Bali <jsbali@gmail.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