Postgres - Pull results from multiple tables based on declared variable

Question

I am new to Postgres. I have 7 tables that have a common field (AcctID). I would like to pull results from each of the tables (without joining) based on the AcctID that I set (e.g. AcctID = '2352').

DO $$
Declare 
    AcctID := '2352';
BEGIN
    Select * from pbx.users where acct = AcctID;
    Select * from pbx.transactions where acct = AcctID;
    Select * from pbx.logs where acct = AcctID;
....
END;
$$;

I get error message that query has no destination. Any recommendation is greatly appreciated.

"pull results" - and do what with them?

Bergi
– Bergi

2022-05-26 14:51:50 +00:00
Commented May 26, 2022 at 14:51 — Bergi
– Bergi, Commented May 26, 2022 at 14:51

Lajos Arpad · Accepted Answer · 2022-05-26 15:05:34Z

0

The error message is essentially telling you: "okay, so you want to pull these results, but then what?"

It is unclear to me why a join is inadequate for you, it would make things so much easier.

For this purpose, you can create a stored procedure, using a composite out parameter, such as the one proposed as an answer to this question: Composite Array Type as OUTPUT parameter in PostgreSQL

Yet, instead of that I really recommend the use of joins, such as

Select * 
from pbx.users
join pbx.transactions on pbx.users.acct = '2352' and pbx.users.acct = pbx.transactions.acct
join pbx.logs on pbx.transactions.acct = pbx.logs.acct;

answered May 26, 2022 at 15:05

Lajos Arpad

80.4k42 gold badges122 silver badges234 bronze badges

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

Collectives™ on Stack Overflow

Postgres - Pull results from multiple tables based on declared variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related