By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test_table (col1 int, col2 varchar, col3 date, col4 date);
INSERT INTO test_table VALUES
(1, 'foo', '2015-01-02', '2015-01-03')
, (2, 'bar', '2015-02-02', '2015-02-03')
, (3, 'baz', '2015-03-02', '2015-03-03')
;
CREATE TABLE
INSERT 0 3
-- Syntax for any Postgres version
CREATE OR REPLACE FUNCTION test_function(date, date, VARIADIC varchar[])
RETURNS SETOF integer
LANGUAGE sql AS
$func$
SELECT col1
FROM test_table
WHERE col3 > $1
AND col4 < $2
AND col2 = ANY($3)
$func$;
CREATE FUNCTION
SELECT * FROM test_function('2015-01-01', '2015-01-20', 'foo', 'bar', 'baz');
-- Safer with explicit type cast
SELECT * FROM test_function('2015-01-01'::date, '2015-01-20'::date, 'foo'::varchar, 'bar', 'baz');
-- Alternative form
SELECT * FROM test_function('2015-01-01', '2015-01-20', VARIADIC '{foo, bar, baz}');
| test_function |
|---|
| 1 |
SELECT 1
| test_function |
|---|
| 1 |
SELECT 1
| test_function |
|---|
| 1 |
SELECT 1
-- SQL-standard function body in Postgres 14+
CREATE OR REPLACE FUNCTION test_function2(date, date, VARIADIC varchar[])
RETURNS SETOF integer
LANGUAGE sql
BEGIN ATOMIC
SELECT col1
FROM test_table
WHERE col3 > $1
AND col4 < $2
AND col2 = ANY($3);
END;
CREATE FUNCTION
SELECT * FROM test_function2('2015-01-01', '2015-01-20', 'foo', 'bar', 'baz');
-- Safer with explicit type cast
SELECT * FROM test_function2('2015-01-01'::date, '2015-01-20'::date, 'foo'::varchar, 'bar', 'baz');
-- Alternative form
SELECT * FROM test_function2('2015-01-01', '2015-01-20', VARIADIC '{foo, bar, baz}');
| test_function2 |
|---|
| 1 |
SELECT 1
| test_function2 |
|---|
| 1 |
SELECT 1
| test_function2 |
|---|
| 1 |
SELECT 1