Thread: cursors in postgres
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;
-----------------------------------------------------
Thanks,
~Jas
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;
-----------------------------------------------------
Thanks,
~Jas
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
On Mar 29, 2007, at 10:47 , Jasbinder Singh Bali wrote:
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
Huh? Why don't you simply declare a unique constraint across the columns you require?
-M
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?
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.zuber@gmail.com> wrote:
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
On Mar 29, 2007, at 10:47 , Jasbinder Singh Bali wrote:
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
Huh? Why don't you simply declare a unique constraint across the columns you require?
-M