Skip site navigation (1) Skip section navigation (2)

Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)

From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)
Date: 2002-05-16 05:16:28
Message-ID: 3CE340AC.7030501@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Tom Lane wrote:
>>I don't understand why this should be rejected, but it does fail for me 
>>also, due to a NULL slot pointer. At what point should it be rejected?
> 
> 
> In the parser.  Ideally, fooid should not even be *visible* while we are
> parsing the arguments to the sibling FROM node.  Compare the handling of
> variable resolution in JOIN/ON clauses --- the namespace gets
> manipulated so that those clauses can't see vars from sibling FROM nodes.
> 

Attached patch takes care of this case. It also passes my previous test 
cases (see below). Applies cleanly to CVS tip and passes all regression 
tests. Please apply if there are no objections.

I'm still working on the second test case from Tom (the NULL slot 
pointer inducing subselect).

Joe

------< tests >-------
test=# \i /opt/src/srf-test.sql
DROP TABLE foo;
DROP
CREATE TABLE foo(fooid int, f2 int);
CREATE
INSERT INTO foo VALUES(1, 11);
INSERT 126218 1
INSERT INTO foo VALUES(2, 22);
INSERT 126219 1
INSERT INTO foo VALUES(1, 111);
INSERT 126220 1
DROP FUNCTION foot(int);
DROP
CREATE FUNCTION foot(int) returns setof foo as 'SELECT * FROM foo WHERE 
fooid = $1;' LANGUAGE SQL;
CREATE

-- should fail with ERROR message
select * from foo, foot(fooid) z where foo.f2 = z.f2;
psql:/opt/src/srf-test.sql:10: ERROR:  Function relation in FROM clause 
may not refer to other relation, "foo"

DROP TABLE foo;
DROP
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary 
key(fooid,foosubid));
psql:/opt/src/srf-test.sql:13: NOTICE:  CREATE TABLE / PRIMARY KEY will 
create implicit index 'foo_pkey' for table 'foo'
CREATE
INSERT INTO foo VALUES(1,1,'Joe');
INSERT 126228 1
INSERT INTO foo VALUES(1,2,'Ed');
INSERT 126229 1
INSERT INTO foo VALUES(2,1,'Mary');
INSERT 126230 1

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  getfoo
--------
       1
(1 row)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  getfoo
--------
       1
(1 row)


-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo 
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  getfoo
--------
       1
       1
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  getfoo
--------
       1
       1
(2 rows)


-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM 
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  getfoo
--------
  Joe
  Ed
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  getfoo
--------
  Joe
  Ed
(2 rows)


-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE 
fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
(1 row)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
(1 row)


-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
DROP
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo 
WHERE fooid = $1;' LANGUAGE SQL;
CREATE
SELECT * FROM getfoo(1) AS t1;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
      1 |        2 | Ed
(2 rows)

DROP VIEW vw_getfoo;
DROP
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
CREATE
SELECT * FROM vw_getfoo;
  fooid | foosubid | fooname
-------+----------+---------
      1 |        1 | Joe
      1 |        2 | Ed
(2 rows)


-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
        dblink_replace
----------------------------
  12345678HelloWorld87654321
(1 row)

DROP VIEW vw_dblink_replace;
DROP
CREATE VIEW vw_dblink_replace AS SELECT * FROM 
dblink_replace('123456789987654321', '99', 'HelloWorld');
CREATE
SELECT * FROM vw_dblink_replace;
        dblink_replace
----------------------------
  12345678HelloWorld87654321
(1 row)


-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
  dblink_get_pkey
-----------------
  fooid
  foosubid
(2 rows)

DROP VIEW vw_dblink_get_pkey;
DROP
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM 
dblink_get_pkey('foo');
CREATE
SELECT * FROM vw_dblink_get_pkey;
  dblink_get_pkey
-----------------
  fooid
  foosubid
(2 rows)


Attachment: srf.2002.05.15.1.patch
Description: text/plain (3.5 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Joe ConwayDate: 2002-05-16 05:30:33
Subject: Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)
Previous:From: DanielDate: 2002-05-15 18:38:20
Subject: Re: Kerberos principal to dbuser mapping

pgsql-patches by date

Next:From: Joe ConwayDate: 2002-05-16 05:30:33
Subject: Re: SRF patch (was Re: [HACKERS] troubleshooting pointers)
Previous:From: Bear GilesDate: 2002-05-16 04:28:01
Subject: more verbose SSL session info for psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group