PostgreSQL array of type table/setof

Question

I need to code a function in postgresql that must 'pick' entire rows from a table depending on a condition and then insert them into an array, and finally return that array.

I'm actually querying the rows I need but there's an special column from the table that I must evaluate first to see if I can retrieve it or not (insert to the array), depending on the type of user is doing the query (customer or worker).

For example:

The special column is named "watchers" and has two variants: 'my_team' and 'only_me' and also has another column named "user_id".

If the column says 'my_team', the row can be inserted into the array with no problems, but if it says 'only_me' I must compare the id of the user calling the function and the user registered in that row, if they match I can insert it into the array, otherwise I can't.

Thing is that as I've read so far it seems that it can't be done with arrays, so I'd like to know if there's another way of doing this, maybe with an extra table or something?

Thanks for your help!

This is the code I have:

CREATE OR REPLACE FUNCTION public.calculates_permission_by_task(task_id integer)
 RETURNS SETOF permission
 LANGUAGE plpgsql
 STABLE
AS $function$
    DECLARE 
        num int := 5;
        record permission;
        ret_permissions permission [];
    BEGIN

        FOR record IN (select
                p.id, p.slug
            from task as t
            join service_request_form as srf on t.service_request_form_id = srf.id
            join service_group as sg on srf.service_group_id = sg.id
            join worker_group as wg on wg.service_group_id = sg.id
            join worker_group_member as wgm on wg.id = wgm.worker_group_id
            join role as r on r.id = wgm.role_id
            join permission_role as pr on r.id = pr.role_id
            join permission as p on p.id = pr.permission_id
            where t.id = task_id
                and wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2'
                and pr.value <> 'off'
            and pr.value <> 'no')
        LOOP
            /* Here is where I pretend to do the comparison and insert data into the array */
            ret_permission := array_append(ret_permissions, record.id);
        END LOOP;
        
        RETURN ret_permissions;
        
    END
$function$

Please show us your existing code, along with sample data and desired results, as tabular text. — GMB
– GMB, Commented Aug 29, 2020 at 12:34
You don't need an array, and you don't need a cursor loop. You don't even need plpgsql, just plain SQL wiill suffice. — wildplasser
– wildplasser, Commented Aug 29, 2020 at 13:47
Note: role is a keyword in Postgres SQL. Don't use it as a table name. — wildplasser
– wildplasser, Commented Aug 29, 2020 at 14:07

GMB · Accepted Answer · 2020-08-29 12:42:45Z

3

You could implement the logic in the query itself.

Assuming that the user_id column you are refering to corresponds to wgm.user_id, that is already in the query, the where clause of the query would become:

where 
    t.id = task_id
    and pr.value not in ('off', 'no')
    and (
        ?.watchers = 'my_team'
        or (
            ?.watchers = 'only_me'
            or wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2'
        )
    )

You don't tell which table watches belong to, so I used ?, which you need to replace that with the relevant table alias.

If there are just two possible values for watchers, then we can simplify the predicates:

where 
    t.id = task_id
    and pr.value not in ('off', 'no')
    and (?.watchers = 'my_team' or wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2')

answered Aug 29, 2020 at 12:42

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Jesús V. Over a year ago

Thanks for your help! I really appreciate it!! But I guess I explained wrong!! :( I said 'watchers' as an example as I wasn't showing the code at first, but the column I must evaluate it;s pr.value which has 'me or 'my_team' values. And the wgm.user_id there is the user that has those rows related, now I gotta see if it's the same user stored in task.worker_id to match de condition I said

wildplasser · Accepted Answer · 2020-08-29 15:02:03Z

You don't need an array
you don't need a cursor loop
You don't even need plpgsql
plain SQL wiill suffice:

CREATE TABLE  permission (
         id integer
        , slug text
        );
INSERT INTO permission(id,slug) VALUES (1,'WTF' );
CREATE TABLE  task (
        id  integer
        , service_request_form_id  integer
        );
INSERT INTO task(id, service_request_form_id) VALUES (1,1 );
CREATE TABLE  service_request_form (
        id integer
        , service_group_id  integer
        );
INSERT INTO service_request_form(id, service_group_id) VALUES (1,1 );
CREATE TABLE  service_group (
        id integer
        );
INSERT INTO service_group(id) VALUES (1);
CREATE TABLE  worker_group (
        id  integer
        , service_group_id  integer
        );
INSERT INTO worker_group(id, service_group_id) VALUES (1,1 );
CREATE TABLE  worker_group_member (
        worker_group_id integer
        , role_id integer
        , user_id text
        );
INSERT INTO worker_group_member(worker_group_id, role_id,user_id) VALUES (1,1,'LFudaU6jzid4SKFlU8MgFAwezyP2' );
CREATE TABLE  zrole (
        id  integer
        );
INSERT INTO zrole(id) VALUES (1 );
CREATE TABLE  permission_role (
        role_id integer
        , permission_id integer
        , value text
        );
INSERT INTO permission_role(role_id,permission_id,value) VALUES (1,1,'Yes' );

CREATE OR REPLACE FUNCTION public.calculates_permission_by_task(task_id integer)
 RETURNS SETOF permission
 LANGUAGE sql STABLE SECURITY INVOKER ROWS 30 -- COST 1
AS $func$

        SELECT p.id, p.slug
        FROM permission as p
        WHERE EXISTS (
                SELECT *
                FROM task as t
                JOIN service_request_form as srf
                         on t.service_request_form_id = srf.id
                JOIN service_group as sg
                        on srf.service_group_id = sg.id
                JOIN worker_group as wg
                        on wg.service_group_id = sg.id
                JOIN worker_group_member as wgm
                        on wg.id = wgm.worker_group_id
                JOIN zrole as zr
                        on zr.id = wgm.role_id
                JOIN permission_role as pr
                        on zr.id = pr.role_id
                WHERE p.id = pr.permission_id
                AND t.id = task_id
                and wgm.user_id = 'LFudaU6jzid4SKFlU8MgFAwezyP2'
                and pr.value NOT IN( 'off' , 'no' )
                -- -------------------------
                -- Add the needed logic
                -- (From @GMB) here
                -- -------------------------
                );
$func$
        ;


    -- Usage:
    -- --------------
SELECT * -- p.id, p.slug
FROM public.calculates_permission_by_task(42);

Collectives™ on Stack Overflow

PostgreSQL array of type table/setof

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related