Is they any way to use postgresql session variable while using select statement

Question

I have a specific request to reflect a user only once if that user has more than one policy,i need a way to left blank duplicated fields and only show real value one time like below:

CREATE OR REPLACE FUNCTION get_user_name(user_pk NUMERIC)
RETURNS text AS
$body$
DECLARE users_array NUMERIC[];
BEGIN
IF (select array_position(users_array, user_pk)) THEN
   return ' ';
ELSE
   users_array = array_append(users_array, user_pk);
   return (SELECT first_name || ' ' || last_name FROM user where id=user_pk);
END IF;
END
$body$
LANGUAGE plpgsql;

SELECT
    row_number() OVER () as id,
    get_user_name(user_pk := users.id) as client,
    policy.policy as policy,
FROM policies_policy as policy
INNER JOIN user users
order by users.id;

I need a way to not re declare users_array every time function called

Hello and Welcome to the SO community, please provide what you've tried to accomplish a solution by yourself. You should also try to form questions out of your key problems. — zero3nna
– zero3nna, Commented Aug 7, 2019 at 10:39
Sample data is better presented as formatted text. See here for some tips to create nice looking table. — user330315
– user330315, Commented Aug 7, 2019 at 11:00
Which of those pictures is your input and which is your output? And what is the relation to the non-existing "session variables"? — user330315
– user330315, Commented Aug 7, 2019 at 11:01

user330315 · Accepted Answer · 2019-08-07 11:14:23Z

1

Assuming the right hand picture is what you want, there is no need for a function:

SELECT case when 
         row_number() OVER (partition by u.id) = 1 then concat_ws(' ', u.first_name, u.last_name)
       end as as name,
       p.policy
FROM policies_policy as policy
  JOIN "user" u on u.id = policy.user_pk
order by u.id;

Or alternatively, simply collect all policies for each user in a comma separated list:

SELECT u.id,
       string_agg(p.policy::text, ',') as policies
FROM policies_policy as policy
  JOIN "user" u on u.id = policy.user_pk
group by u.id
order by u.id;

answered Aug 7, 2019 at 11:14

user330315

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

Collectives™ on Stack Overflow

Is they any way to use postgresql session variable while using select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related