In psql how to run a Loop for a Select query with CTEs and get the output shown if I run it in a read-only db?

user_2766803 0
Viewed 70

My initial question is posted here (In psql how to run a Loop for a Select query with CTEs and get the output shown in read-only db?), which isn't defined well, so I am creating new question here. I want to know how can I use a loop variable (or something similar) inside a Select query with CTEs . I hope the following is a minimal reproducible example:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
insert into persons values (4,'Smith','Eric','713 Louise Circle','Paris');
insert into persons values (5,'Smith2','Eric2','715 Louise Circle','London');
insert into persons values (8,'Smith3','Eric3','718 Louise Circle','Madrid');

Now I run the following for different values of (1,2,3)

WITH params AS 
( 
       SELECT <ROWNUMBER> AS rownumber ),
person AS 
( 
         SELECT   personid, lastname, firstname, address 
         FROM     params, persons 
         ORDER BY personid DESC 
         LIMIT 1 
         OFFSET ( SELECT rownumber - 1 
                  FROM   params) ), 
filtered AS 
( 
       SELECT * 
       FROM   person 
       WHERE  address ~ (SELECT rownumber::text FROM params)
)
SELECT * 
FROM   filtered;

and getting these outputs respectively for 1,2 and 3:


| personid | lastname | firstname |      address
|----------|----------|-----------|-------------------
|        8 | Smith3   | Eric3     | 718 Louise Circle
(1 row)

| personid | lastname | firstname | address
|----------|----------|-----------|---------
(0 rows)

| personid | lastname | firstname |      address
|----------|----------|-----------|-------------------
|        4 | Smith    | Eric      | 713 Louise Circle
(1 row)

My goal is to have a single query with loop or any other means to get the union of all 3 above select runs. I only have read-only access to db, so can't output in a new table. The GUI software I use have options to output in an internal window or export to a plain text file. The desired result would be:

|personid  | lastname | firstname |      address
|----------|----------|-----------|-------------------
|        4 | Smith    | Eric      | 713 Louise Circle
|        8 | Smith3   | Eric3     | 718 Louise Circle
(2 rows)

In reality the the loop variable is used in a more complicated way.

postgresql loops
1 Answers

If I decipher this right, you basically want to select all people where the row number according to the descending ID appears in the address. The final result should then be limited to certain of these row numbers.

Then you don't need to use that cumbersome LIMIT/OFFSET construct at all. You can simply use the row_number() window function.

To filter for the row numbers you can simply use IN. Depending on what you want here you can either use a list of literals, especially if the numbers aren't consecutive. Or you can use generate_series() to generate a list of consecutive numbers. Of course you can also use a subquery, when the numbers are stored in another table.

With a list of literals that would look something like this:

SELECT pn.personid,
       pn.lastname,
       pn.firstname,
       pn.address,
       pn.city
       FROM (SELECT p.personid,
                    p.lastname,
                    p.firstname,
                    p.address,
                    p.city,
                    row_number() OVER (ORDER BY p.personid DESC) n
                    FROM persons p) pn
       WHERE pn.address LIKE concat('%', pn.n, '%')
             AND pn.n IN (1, 2, 4);

If you want to use generate_series() an example would be:

SELECT pn.personid,
       pn.lastname,
       pn.firstname,
       pn.address,
       pn.city
       FROM (SELECT p.personid,
                    p.lastname,
                    p.firstname,
                    p.address,
                    p.city,
                    row_number() OVER (ORDER BY p.personid DESC) n
                    FROM persons p) pn
       WHERE pn.address LIKE concat('%', pn.n, '%')
             AND pn.n IN (SELECT s.n
                                 FROM generate_series(1, 3) s (n));

And a subquery of another table could be used like so:

SELECT pn.personid,
       pn.lastname,
       pn.firstname,
       pn.address,
       pn.city
       FROM (SELECT p.personid,
                    p.lastname,
                    p.firstname,
                    p.address,
                    p.city,
                    row_number() OVER (ORDER BY p.personid DESC) n
                    FROM persons p) pn
       WHERE pn.address LIKE concat('%', pn.n, '%')
             AND pn.n IN (SELECT t.nmuloc
                                 FROM elbat t);

For larger sets of numbers you can also consider to use an INNER JOIN on the numbers instead of IN.

Using generate_series():

SELECT pn.personid,
       pn.lastname,
       pn.firstname,
       pn.address,
       pn.city
       FROM (SELECT p.personid,
                    p.lastname,
                    p.firstname,
                    p.address,
                    p.city,
                    row_number() OVER (ORDER BY p.personid DESC) n
                    FROM persons p) pn
            INNER JOIN generate_series(1, 1000000) s (n)
                       ON s.n = pn.n
       WHERE pn.address LIKE concat('%', pn.n, '%');

Or when the numbers are in another table:

SELECT pn.personid,
       pn.lastname,
       pn.firstname,
       pn.address,
       pn.city
       FROM (SELECT p.personid,
                    p.lastname,
                    p.firstname,
                    p.address,
                    p.city,
                    row_number() OVER (ORDER BY p.personid DESC) n
                    FROM persons p) pn
            INNER JOIN elbat t
                       ON t.nmuloc = pn.n
       WHERE pn.address LIKE concat('%', pn.n, '%');

Note that I also changed the regular expression pattern matching to a simple LIKE. That would make the queries a bit more portable. But you can of course replace that by any expression you really need.

db<>fiddle (with some of the variants)

user_96614240
Related