Re: cursors in postgres

Actually I'm doing a duplicate check
My function accepts 4 parameters.
If all four exist in a particular row then i should not be inserting that
record again.

so is
INSERT INTO table(a,b,c) SELECT 'a','b','c' WHERE NOT EXISTS
( SELECT * FROM table WHERE (a,b,c) = ('a','b','c') );
going to solve my problem?

On 3/29/07, Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> wrote:
>
> 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
>