Create stored procedure with optional parameters in postgres

Question

I am trying to create either a function or a stored procedure in a Postgres database. I don't really know the syntax in Postgres. I am trying the following, but I know it is not complete.

Create or replace Procedure sp_testSproc(
    /* Input Parameters */
    param1 varchar,
    param2 varchar,
    param3 varchar,
    param4 timestamp,
    param5 timestamp,
    param6 int,
    param7 varchar)

I think the way to create this is to declare a variable to represent the SQL select statement to be executed for example

sqlQueryToExecute =  'select * from myTable where 1=1'

if (param1 Is Not NUll)
    sqlQueryToExecute += 'And param1 = param1Variable'

if (param2 Is Not NUll)
    sqlQueryToExecute += 'And param2 = param2Variable'

return result

Can someone help with an example stored procedure or function to make this work please?

say a table have 5 column, you query like select * from table where col1 = param1 and col2 = param2.... And if param1 not exists then where part(col1 = param1) will be ignored. Is that you want? — jian, Nov 16 '22 at 05:30
yes, that is correct. I want to do a select using any of the available paramters — Chiefster, Nov 16 '22 at 05:38
Sorry, you will have to read the documentation to learn how to write a database function. You cannot expect a tutorial here. — Laurenz Albe, Nov 16 '22 at 07:21

score 0 · Answer 1 · answered Nov 16 '22 at 07:28

References:
PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement Functions with variable number of input parameters
Test for null in function with varying parameters
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
https://www.postgresql.org/docs/current/xfunc-overload.html

based on the above reference links you can write a function can take variable number of input arguments and return a table.

Setup.

CREATE TABLE test7 (
    col1 text,
    col2 text,
    col3 text,
    col4 timestamp,
    col5 timestamp,
    col6 int,
    col7 text
);

INSERT INTO test7
SELECT
    i::text,
    (i + 1)::text,
    (i % 5)::text,
    '2022-11-16 10:00:00'::timestamp + i * INTERVAL '30 minute',
    '2022-11-16 10:00:00'::timestamp + i * INTERVAL '60 minute',
    (random() * 10)::int,
    i::text || '_' || (i % 5)::text
FROM
    generate_series(1, 10) _ (i);

function:

CREATE OR REPLACE FUNCTION test7_where (_col1 text DEFAULT NULL, _col2 text DEFAULT NULL, _col3 text DEFAULT NULL, _col4 timestamp DEFAULT NULL, _col5 timestamp DEFAULT NULL, _col6 int DEFAULT NULL, _col7 text DEFAULT NULL)
    RETURNS TABLE (
        col text,
        col2 text,
        col3 text,
        col4 timestamp,
        col5 timestamp,
        col6 int,
        col7 text
    )
    AS $func$
DECLARE
    _sql text;
BEGIN
    _sql := concat_ws(' and ', CASE WHEN $1 IS NOT NULL THEN
            'col1 = $1'
        END, CASE WHEN $2 IS NOT NULL THEN
            'col2 = $2'
        END, CASE WHEN $3 IS NOT NULL THEN
            'col3 = $3'
        END, CASE WHEN $4 IS NOT NULL THEN
            'col4 = $4'
        END, CASE WHEN $5 IS NOT NULL THEN
            'col5 = $5'
        END, CASE WHEN $6 IS NOT NULL THEN
            'col6 = $6'
        END, CASE WHEN $7 IS NOT NULL THEN
            'col7 = $7'
        END);
    RAISE NOTICE '_sql:%', _sql;
    RETURN QUERY EXECUTE concat($$
        SELECT
            * FROM test7
            WHERE
                $$, _sql)
    USING $1, $2, $3, $4, $5, $6, $7;
END
$func$
LANGUAGE plpgsql;

Call it:

SELECT
    *
FROM
    test7_where (_col4 => '2022-11-16 15:00:00', _col5 => '2022-11-16 20:00:00');

SELECT
    *
FROM
    test7_where (3::text, 4::text, 3::text, '2022-11-16 11:30:00'::timestamp, '2022-11-16 13:00:00'::timestamp, 8, '3_3'::text);

Create stored procedure with optional parameters in postgres

1 Answers1