SELECT * FROM pg_settings WHERE name LIKE 'enable%'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val -------------------+---------+------+---------------------------------------------+--------------------------------------------------------+------------+---------+---------+---------+---------+--------- enable_bitmapscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of bitmap-scan plans. | | user | bool | default | | enable_hashagg | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of hashed aggregation plans. | | user | bool | default | | enable_hashjoin | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of hash join plans. | | user | bool | default | | enable_indexscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of index-scan plans. | | user | bool | default | | enable_mergejoin | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of merge join plans. | | user | bool | default | | enable_nestloop | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of nested-loop join plans. | | user | bool | default | | enable_seqscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of sequential-scan plans. | | user | bool | default | | enable_sort | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of explicit sort steps. | | user | bool | default | | enable_tidscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of TID scan plans. | | user | bool | default | | (9 rows) CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); INSERT INTO foo2 VALUES(2, 22); INSERT INTO foo2 VALUES(1, 111); CREATE FUNCTION foot(int) returns setof foo2 as ' "SELECT * FROM foo2 WHERE fooid = #{args[0]}"' language 'plruby'; select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; ERROR: function expression in FROM may not refer to other relations of same query level select * from foo2 where f2 in ( select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid ) ORDER BY 1,2; fooid | f2 -------+----- 1 | 11 1 | 111 2 | 22 (3 rows) select * from foo2 where f2 in ( select f2 from foot(1) z where z.fooid = foo2.fooid ) ORDER BY 1,2; fooid | f2 -------+----- 1 | 11 1 | 111 (2 rows) select * from foo2 where f2 in ( select f2 from foot(foo2.fooid) z where z.fooid = 1 ) ORDER BY 1,2; fooid | f2 -------+----- 1 | 11 1 | 111 (2 rows) select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; fooid | f2 -------+----- 1 | 11 1 | 111 (2 rows) CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" INSERT INTO foo VALUES(1,1,'Joe'); INSERT INTO foo VALUES(1,2,'Ed'); INSERT INTO foo VALUES(2,1,'Mary'); CREATE FUNCTION getfoo(int) RETURNS int AS ' args[0] ' language 'plruby'; SELECT * FROM getfoo(1) AS t1; t1 ---- 1 (1 row) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; getfoo -------- 1 (1 row) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof int AS ' "SELECT fooid FROM foo WHERE fooid = #{args[0]}" ' language 'plruby'; SELECT * FROM getfoo(1) AS t1; t1 ---- 1 1 (2 rows) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; getfoo -------- 1 1 (2 rows) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof text AS ' "SELECT fooname FROM foo WHERE fooid = #{args[0]}" ' language 'plruby'; SELECT * FROM getfoo(1) AS t1; t1 ----- Joe Ed (2 rows) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; getfoo -------- Joe Ed (2 rows) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof foo AS ' "SELECT * FROM foo WHERE fooid = #{args[0]}" ' language 'plruby'; SELECT * FROM getfoo(1) AS t1; fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof record AS ' "SELECT * FROM foo WHERE fooid = #{args[0]}" ' language 'plruby'; SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text); fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS (fooid int, foosubid int, fooname text); SELECT * FROM vw_getfoo; fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof int AS ' PL.exec("SELECT fooid FROM foo WHERE fooid = #{args[0]}") do |row| yield row.values end ' language 'plruby'; SELECT * FROM getfoo(1) AS t1; t1 ---- 1 1 (2 rows) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; getfoo -------- 1 1 (2 rows) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); CREATE FUNCTION getfoo(int) RETURNS setof foo AS ' PL.exec("SELECT * FROM foo WHERE fooid = #{args[0]}", nil, "value") do |r| yield r end ' language 'plruby'; SELECT * FROM getfoo(1) AS t1; fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); DROP FUNCTION foot(int); DROP TABLE foo2; DROP TABLE foo; CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan" INSERT INTO foorescan values(5000,1,'abc.5000.1'); INSERT INTO foorescan values(5001,1,'abc.5001.1'); INSERT INTO foorescan values(5002,1,'abc.5002.1'); INSERT INTO foorescan values(5003,1,'abc.5003.1'); INSERT INTO foorescan values(5004,1,'abc.5004.1'); INSERT INTO foorescan values(5005,1,'abc.5005.1'); INSERT INTO foorescan values(5006,1,'abc.5006.1'); INSERT INTO foorescan values(5007,1,'abc.5007.1'); INSERT INTO foorescan values(5008,1,'abc.5008.1'); INSERT INTO foorescan values(5009,1,'abc.5009.1'); INSERT INTO foorescan values(5000,2,'abc.5000.2'); INSERT INTO foorescan values(5001,2,'abc.5001.2'); INSERT INTO foorescan values(5002,2,'abc.5002.2'); INSERT INTO foorescan values(5003,2,'abc.5003.2'); INSERT INTO foorescan values(5004,2,'abc.5004.2'); INSERT INTO foorescan values(5005,2,'abc.5005.2'); INSERT INTO foorescan values(5006,2,'abc.5006.2'); INSERT INTO foorescan values(5007,2,'abc.5007.2'); INSERT INTO foorescan values(5008,2,'abc.5008.2'); INSERT INTO foorescan values(5009,2,'abc.5009.2'); INSERT INTO foorescan values(5000,3,'abc.5000.3'); INSERT INTO foorescan values(5001,3,'abc.5001.3'); INSERT INTO foorescan values(5002,3,'abc.5002.3'); INSERT INTO foorescan values(5003,3,'abc.5003.3'); INSERT INTO foorescan values(5004,3,'abc.5004.3'); INSERT INTO foorescan values(5005,3,'abc.5005.3'); INSERT INTO foorescan values(5006,3,'abc.5006.3'); INSERT INTO foorescan values(5007,3,'abc.5007.3'); INSERT INTO foorescan values(5008,3,'abc.5008.3'); INSERT INTO foorescan values(5009,3,'abc.5009.3'); INSERT INTO foorescan values(5000,4,'abc.5000.4'); INSERT INTO foorescan values(5001,4,'abc.5001.4'); INSERT INTO foorescan values(5002,4,'abc.5002.4'); INSERT INTO foorescan values(5003,4,'abc.5003.4'); INSERT INTO foorescan values(5004,4,'abc.5004.4'); INSERT INTO foorescan values(5005,4,'abc.5005.4'); INSERT INTO foorescan values(5006,4,'abc.5006.4'); INSERT INTO foorescan values(5007,4,'abc.5007.4'); INSERT INTO foorescan values(5008,4,'abc.5008.4'); INSERT INTO foorescan values(5009,4,'abc.5009.4'); INSERT INTO foorescan values(5000,5,'abc.5000.5'); INSERT INTO foorescan values(5001,5,'abc.5001.5'); INSERT INTO foorescan values(5002,5,'abc.5002.5'); INSERT INTO foorescan values(5003,5,'abc.5003.5'); INSERT INTO foorescan values(5004,5,'abc.5004.5'); INSERT INTO foorescan values(5005,5,'abc.5005.5'); INSERT INTO foorescan values(5006,5,'abc.5006.5'); INSERT INTO foorescan values(5007,5,'abc.5007.5'); INSERT INTO foorescan values(5008,5,'abc.5008.5'); INSERT INTO foorescan values(5009,5,'abc.5009.5'); CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS ' "SELECT * FROM foorescan WHERE fooid >= #{args[0]} and fooid < #{args[1]}" ' language 'plruby'; SELECT * FROM foorescan f WHERE f.fooid IN ( SELECT fooid FROM foorescan(5002,5004) ) ORDER BY 1,2; fooid | foosubid | fooname -------+----------+------------ 5002 | 1 | abc.5002.1 5002 | 2 | abc.5002.2 5002 | 3 | abc.5002.3 5002 | 4 | abc.5002.4 5002 | 5 | abc.5002.5 5003 | 1 | abc.5003.1 5003 | 2 | abc.5003.2 5003 | 3 | abc.5003.3 5003 | 4 | abc.5003.4 5003 | 5 | abc.5003.5 (10 rows) CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004); SELECT * FROM foorescan f WHERE f.fooid IN ( SELECT fooid FROM vw_foorescan ) ORDER BY 1,2; fooid | foosubid | fooname -------+----------+------------ 5002 | 1 | abc.5002.1 5002 | 2 | abc.5002.2 5002 | 3 | abc.5002.3 5002 | 4 | abc.5002.4 5002 | 5 | abc.5002.5 5003 | 1 | abc.5003.1 5003 | 2 | abc.5003.2 5003 | 3 | abc.5003.3 5003 | 4 | abc.5003.4 5003 | 5 | abc.5003.5 (10 rows) CREATE TABLE barrescan (fooid int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan" INSERT INTO barrescan values(5003); INSERT INTO barrescan values(5004); INSERT INTO barrescan values(5005); INSERT INTO barrescan values(5006); INSERT INTO barrescan values(5007); INSERT INTO barrescan values(5008); CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS ' "SELECT * FROM foorescan WHERE fooid = #{args[0]}" ' language 'plruby'; SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN ( SELECT fooid FROM foorescan(b.fooid) ) ORDER BY 1,2; fooid | foosubid | fooname -------+----------+------------ 5003 | 1 | abc.5003.1 5003 | 2 | abc.5003.2 5003 | 3 | abc.5003.3 5003 | 4 | abc.5003.4 5003 | 5 | abc.5003.5 5004 | 1 | abc.5004.1 5004 | 2 | abc.5004.2 5004 | 3 | abc.5004.3 5004 | 4 | abc.5004.4 5004 | 5 | abc.5004.5 5005 | 1 | abc.5005.1 5005 | 2 | abc.5005.2 5005 | 3 | abc.5005.3 5005 | 4 | abc.5005.4 5005 | 5 | abc.5005.5 5006 | 1 | abc.5006.1 5006 | 2 | abc.5006.2 5006 | 3 | abc.5006.3 5006 | 4 | abc.5006.4 5006 | 5 | abc.5006.5 5007 | 1 | abc.5007.1 5007 | 2 | abc.5007.2 5007 | 3 | abc.5007.3 5007 | 4 | abc.5007.4 5007 | 5 | abc.5007.5 5008 | 1 | abc.5008.1 5008 | 2 | abc.5008.2 5008 | 3 | abc.5008.3 5008 | 4 | abc.5008.4 5008 | 5 | abc.5008.5 (30 rows) SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN ( SELECT fooid FROM foorescan(b.fooid) ) GROUP BY b.fooid ORDER BY 1,2; fooid | max -------+----- 5003 | 5 5004 | 5 5005 | 5 5006 | 5 5007 | 5 5008 | 5 (6 rows) CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN ( SELECT fooid FROM foorescan(b.fooid) ) ORDER BY 1,2; SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; fooid | foosubid | fooname -------+----------+------------ 5004 | 1 | abc.5004.1 5004 | 2 | abc.5004.2 5004 | 3 | abc.5004.3 5004 | 4 | abc.5004.4 5004 | 5 | abc.5004.5 (5 rows) CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN ( SELECT fooid FROM foorescan(b.fooid) ) GROUP BY b.fooid ORDER BY 1,2; SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5; fooid | maxsubid -------+---------- 5003 | 5 5004 | 5 5005 | 5 5006 | 5 5007 | 5 5008 | 5 (6 rows) DROP VIEW vw_foorescan; DROP VIEW fooview1; DROP VIEW fooview2; DROP FUNCTION foorescan(int,int); DROP FUNCTION foorescan(int); DROP TABLE foorescan; DROP TABLE barrescan;