Re: SRF patch (was Re: [HACKERS] troubleshooting pointers) - Mailing list pgsql-patches

Tom Lane wrote:
> I've committed this with some revisions.  The VIEW cases you were
> worried about seem to work now.  I think you'll find that
> single-FROM-item cases generally work, and it's time to start worrying
> about joins (ie, rescans).

Hi Tom,

I've been looking through the SRF patch as-committed, and I think I
understand most of your changes, but I have a question: FunctionNext()
now seems to *always* use a tuplestore instead of conditionally using
the store only for rescans, or if the function was explicitly marked as
PM_MATERIALIZE. Do you still think there should be an option to project
tuples without first storing them, or should we eliminate the notion of
function mode and always materialize?


>
> Parameters also need thought.  This should be rejected:
>
> regression=# select * from foo, foot(fooid) z where foo.f2 = z.f2;
> server closed the connection unexpectedly

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?


>
> On the other hand, IMHO this should work:
>
> regression=# select * from foo where f2 in
> regression-# (select f2 from foot(foo.fooid) z where z.fooid = foo.fooid);
> server closed the connection unexpectedly

This also fails in (based on a quick look) exactly the same way -- a
NULL slot pointer (econtext->ecxt_scantuple) passed to ExecEvalVar().


BTW, The test cases I was using previously now all pass (copy below).

Thanks,

Joe


DROP TABLE foo;
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');

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

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

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

-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE
fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

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

-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
DROP VIEW vw_dblink_replace;
CREATE VIEW vw_dblink_replace AS SELECT * FROM
dblink_replace('123456789987654321', '99', 'HelloWorld');
SELECT * FROM vw_dblink_replace;

-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
DROP VIEW vw_dblink_get_pkey;
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM
dblink_get_pkey('foo');
SELECT * FROM vw_dblink_get_pkey;