Re: plpgsql mixing variable in SELECT INTO ?

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
Cc: "postgresql" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, March 03, 2003 4:52 PM
Subject: Re: [GENERAL] plpgsql mixing variable in SELECT INTO ?

> "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)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(at)postgresql(dot)org
>