Select query inside case in a postgresql function

Question

I've been looking for a while, try a lot of codes found in SO, but none of them worked. So I'm asking a new question.

In a PostgreSQL function, I'm trying to return a SETOF bigint (bunch of ids from a table), but depending on first input ($1) the query would be diferent. So I have also a SELECT CASE in that function.

Currently, the function looks like this:

CREATE OR REPLACE FUNCTION get_employee_ids(int, int, int, int, text, int)
RETURNS SETOF bigint AS
$BODY$
    SELECT CASE 
        WHEN $1 = 1 THEN
            SELECT employee_id FROM employee WHERE period = $2 AND payment >= $3 AND operation = $4
            INTERSECT
            SELECT employee_id FROM employee WHERE period IN $5
        WHEN $1 = 2 THEN
            SELECT employee_id FROM employee WHERE period BETWEEN $1 AND $6 AND payment >= $2 AND operation = $3
            INTERSECT
            SELECT employee_id FROM employee WHERE period IN $5
        WHEN $1 = 3 THEN
            SELECT employee_id FROM employee WHERE period BETWEEN $1 AND $6 AND payment >= $2 AND operation != $3
            INTERSECT
            SELECT employee_id FROM employee WHERE period IN $5
        WHEN $1 = 4 THEN
            SELECT employee_id FROM employee WHERE period BETWEEN $1 AND $6 AND payment < $2 AND operation = $3
            INTERSECT
            SELECT employee_id FROM employee WHERE period IN $5
    END
$BODY$
LANGUAGE sql VOLATILE;

In this case, the error is a syntax error near SELECT. The problem seems to be that the SELECT is not executing. But I tried also with RETURN QUERY, EXECUTE, RETURN QUERY EXECUTE and lot of other things from other answers here.

How can I make this work?

Edit: Useful info, this is how I use the function, with these parameters: get_employee_ids(4, 1108, 250, 97, "(1109,1110)", 0808)

Already tried, syntax error near $5 when surronding the whole SELECT and syntax error near INTERSECT when surronding each SELECT. — AleOtero93
– AleOtero93, Commented Oct 27, 2015 at 12:48
. . Oh, that is another problem as well. I would suggest that you just use dynamic SQL> — Gordon Linoff
– Gordon Linoff, Commented Oct 27, 2015 at 12:52

Community · Accepted Answer · 2017-05-23 12:23:24Z

2

Use the integer array as suggested by @klin and this much simpler query:

create or replace function get_employee_ids (
    int, int, int, int, int[], int
) returns setof bigint as
$body$

select employee_id
from employee
where
    (
        ($1 in (1, 4) and payment < $2 and operation = $3)
        or
        ($1 = 2 and payment >= $2 and operation = $3)
        or
        ($1 = 3 and payment >= $2 and operation != $3)
    )
    and period = ANY ($5)
    and period between $1 and $6

$body$
language sql volatile;

BTW it looks like it can be stable in instead of volatile

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Oct 27, 2015 at 13:45

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

klin · Accepted Answer · 2015-10-27 13:22:57Z

The fifth argument is a text which is to be a part of a query. You cannot build the query this way. Change the parameter type to integer[] and use ANY instead of IN.

CREATE OR REPLACE FUNCTION get_employee_ids(int, int, int, int, int[], int)
RETURNS SETOF bigint AS
$BODY$
    SELECT CASE 
        WHEN $1 = 1 THEN
            (SELECT employee_id FROM employee WHERE period = $2 AND payment >= $3 AND operation = $4
            INTERSECT
            SELECT employee_id FROM employee WHERE period = ANY ($5))
        WHEN $1 = 2 THEN
            (SELECT employee_id FROM employee WHERE period BETWEEN $1 AND $6 AND payment >= $2 AND operation = $3
            INTERSECT
            SELECT employee_id FROM employee WHERE period = ANY ($5))
        WHEN $1 = 3 THEN
            (SELECT employee_id FROM employee WHERE period BETWEEN $1 AND $6 AND payment >= $2 AND operation != $3
            INTERSECT
            SELECT employee_id FROM employee WHERE period = ANY ($5))
        WHEN $1 = 4 THEN
            (SELECT employee_id FROM employee WHERE period BETWEEN $1 AND $6 AND payment < $2 AND operation = $3
            INTERSECT
            SELECT employee_id FROM employee WHERE period = ANY ($5))
    END
$BODY$
LANGUAGE sql VOLATILE;

-- usage:
SELECT get_employee_ids(4, 1108, 250, 97, array[1109,1110], 0808);

Collectives™ on Stack Overflow

Select query inside case in a postgresql function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related