Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example of dynamic queries inside plpgsql stored procedures
CREATE TABLE IF NOT EXISTS sample_table (
id serial,
field1 text,
field2 int,
field3 date
);
INSERT INTO sample_table (field1, field2, field3)
SELECT
md5(random()::text),
(random() * 1000)::int,
now() - (((random() * 1000)::int - (random() * 1000)::int) || ' days')::interval
FROM generate_series(1, 1000);
CREATE OR REPLACE FUNCTION sample_stored_procedure (
_order_field text = 'id',
_order text = 'ASC',
_limit int = 10,
_offset int = 0
) RETURNS SETOF sample_table AS $$
DECLARE
sql text := 'SELECT * FROM sample_table';
BEGIN
IF _order NOT IN ('ASC', 'DESC') THEN
_order := 'ASC';
END IF;
IF _order_field NOT IN ('id', 'field1', 'field2') THEN
_order_field := 'id';
END IF;
sql := sql || ' ORDER BY ' || _order_field || ' ' || _order;
IF _offset < 0 THEN
_offset := 0;
END IF;
IF _limit < 1 OR _limit > 1000 THEN
_limit := 10;
END IF;
sql := sql || ' LIMIT $1 OFFSET $2';
RETURN QUERY EXECUTE sql USING _limit, _offset;
END
$$ LANGUAGE plpgsql;
SELECT * FROM sample_stored_procedure();
SELECT * FROM sample_stored_procedure('field1');
SELECT * FROM sample_stored_procedure('field1', 'DESC');
SELECT * FROM sample_stored_procedure('field2', 'ASC', 5);
SELECT * FROM sample_stored_procedure('field2', 'ASC', 5, 5);
@lmineiro

This comment has been minimized.

Copy link
Owner Author

lmineiro commented Oct 9, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.