Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement