Thread: plpgsql mixing variable in SELECT INTO ?
Hi All I think i found the problem to my previous thread. Since upgrading to 7.3.2 from 7.3.1 im having the following problem. The following line works: SELECT * INTO lft_rgt FROM forum_tree WHERE node_id = v_node_id; RETURN lft_rgt.f_id; The following lines dont work ( variable get mixed ), lft_rgt.f_id return lft_rgt.rgt :( SELECT lft, rgt, f_id INTO lft_rgt FROM forum_tree WHERE node_id = v_node_id; RETURN lft_rgt.f_id; Any ideas ? maybe bug ? -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://sites.canaan.co.il --------------------------
"Ben-Nes Michael" <miki@canaan.co.il> writes: > The following line works: > SELECT * INTO lft_rgt FROM forum_tree WHERE node_id = v_node_id; > RETURN lft_rgt.f_id; > The following lines dont work ( variable get mixed ), lft_rgt.f_id return > lft_rgt.rgt :( > SELECT lft, rgt, f_id INTO lft_rgt FROM forum_tree WHERE node_id = > v_node_id; > RETURN lft_rgt.f_id; If this is meant as a bug report, it is entirely useless. Please give a complete, standalone example that someone else can run to reproduce the problem. You can't expect us to guess our way to all the context involved in this query. The bug reporting guidelines in the User's Guide http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/bug-reporting.html are a tad verbose but are worth reading. regards, tom lane
Hi Tom, here is simple example of the problem. ver 7.3.2 CREATE TABLE test ( col1 int, col2 int, col3 int ); INSERT INTO test VALUES (1, 2, 3); CREATE FUNCTION test() RETURNS INTEGER AS ' DECLARE var1 test%ROWTYPE; BEGIN SELECT col3, col2 INTO var1 FROM test; RETURN var1.col2||var1.col3; END;' LANGUAGE 'plpgsql'; # SELECT test(); test ------ (1 row) the result of the function is empty string. If i change the select to * or add col1 ( SELECT col1, col3, col2 INTO var1 FROM test; ) the function will work fine. so it seems that order does matter and one canot ommit the first field in the table structure. Hope this can help -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: "postgresql" <pgsql-general@postgresql.org> Sent: Monday, March 03, 2003 4:52 PM Subject: Re: [GENERAL] plpgsql mixing variable in SELECT INTO ? > "Ben-Nes Michael" <miki@canaan.co.il> writes: > > The following line works: > > SELECT * INTO lft_rgt FROM forum_tree WHERE node_id = v_node_id; > > RETURN lft_rgt.f_id; > > The following lines dont work ( variable get mixed ), lft_rgt.f_id return > > lft_rgt.rgt :( > > SELECT lft, rgt, f_id INTO lft_rgt FROM forum_tree WHERE node_id = > > v_node_id; > > RETURN lft_rgt.f_id; > > If this is meant as a bug report, it is entirely useless. Please give a > complete, standalone example that someone else can run to reproduce the > problem. You can't expect us to guess our way to all the context > involved in this query. > > The bug reporting guidelines in the User's Guide > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/bug-reporting.ht ml > are a tad verbose but are worth reading. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
"Ben-Nes Michael" <miki@canaan.co.il> writes:
> so it seems that order does matter and one canot ommit the first field in
> the table structure.
What's surprising about that? That's exactly how it *should* work, IMHO.
regards, tom lane
Hi You mean I cant use the full power of the SELECT as mentioned in the manual and when using SELECT INTO I must use * and I can refine my query to some of the fields ? Also its quite strange that my code worked in 7.3.1- Cheers -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: "postgresql" <pgsql-general@postgresql.org> Sent: Tuesday, March 04, 2003 5:16 PM Subject: Re: [GENERAL] plpgsql mixing variable in SELECT INTO ? > "Ben-Nes Michael" <miki@canaan.co.il> writes: > > so it seems that order does matter and one canot ommit the first field in > > the table structure. > > What's surprising about that? That's exactly how it *should* work, IMHO. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
"Ben-Nes Michael" <miki@canaan.co.il> writes:
> You mean I cant use the full power of the SELECT as mentioned in the manual
> and when using SELECT INTO I must use * and I can refine my query to some of
> the fields ?
Certainly you can. But when you assign into a row variable, you have to
provide all the row variable's columns, in order. How else would you
expect it to work?
> Also its quite strange that my code worked in 7.3.1-
I don't believe it.
regards, tom lane
Maybe he is looking at a shorthand for assigning a row variable to an other... Tom Lane wrote: > > "Ben-Nes Michael" <miki@canaan.co.il> writes: > > You mean I cant use the full power of the SELECT as mentioned in the manual > > and when using SELECT INTO I must use * and I can refine my query to some of > > the fields ? > > Certainly you can. But when you assign into a row variable, you have to > provide all the row variable's columns, in order. How else would you > expect it to work? > > > Also its quite strange that my code worked in 7.3.1- > > I don't believe it. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
well i coded select into lft_rgt rgt from forum_tree where bla bla; This was code i implented at 7.2.x and rgt suposed to tell me how to push a nested set model of tree ( Joe Celko Model ) I had to fix the "rgt" to "*" after i updated to 7.3.2 -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: "postgresql" <pgsql-general@postgresql.org> Sent: Tuesday, March 04, 2003 7:45 PM Subject: Re: [GENERAL] plpgsql mixing variable in SELECT INTO ? > "Ben-Nes Michael" <miki@canaan.co.il> writes: > > You mean I cant use the full power of the SELECT as mentioned in the manual > > and when using SELECT INTO I must use * and I can refine my query to some of > > the fields ? > > Certainly you can. But when you assign into a row variable, you have to > provide all the row variable's columns, in order. How else would you > expect it to work? > > > Also its quite strange that my code worked in 7.3.1- > > I don't believe it. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Hello people, what are your experiences with storing binary data in the db (pictures or xml - data). What are the pro and contra? Thanks for answers. Greets Michael