How do i SELECT multiple Values with postgreSQL INTO multiple variables Depending on a CASE?

Question

i was wondering if it is possible to use a CASE Expression inside a SELECT statement in postgres where i write multiple values values at once.

So for example:

DO
$$
DECLARE
    var int := 1;
    val1 int;
BEGIN
    SELECT
    CASE var
    WHEN 1 THEN
        variable1, variable2
    WHEN 2 THEN
        variable3, variable4
    INTO 
        val1, val2
    FROM mytable;
$$

The thing is it works well when using this:

DO
$$
DECLARE
    var int := 1;
    val1 int;
    val2 int;
BEGIN
    SELECT
    CASE var
    WHEN 1 THEN
        variable1
    WHEN 2 THEN
        variable3
    INTO 
        val1
    FROM mytable;
$$

i know i can just move the CASE outside the SELECT statement and just write two SELECT statements but this seems to be more to write then the solution i want.

Another thing i tried was:

DO
$$
DECLARE
    var int := 1;
    val1 int;
BEGIN
    SELECT
    CASE var
    WHEN 1 THEN
        (variable1, variable2)
    WHEN 2 THEN
        (variable3, variable4)
    INTO 
        val1, val2
    FROM mytable;
$$

but this seems to write (variable1, variable2) into val1 and fails because of wrong datatype.

I Guess with the last solution i could write the values into an array, but i need them in separate variables.

Any tips are appreciated.

Dan · Accepted Answer · 2020-03-13 23:21:25Z

There are three ways that I could think about. Two are really easy, one is complex but related to (variable1, variable2).

CASE usage (easy)
JSONBusage (easy)
TYPE usage (complex)

FIRST OPTION: CASE usage

This one is the easiest, but not really optimized option.

When you use SELECT vals INTO vars you must have the same quantity of vals and vars. So, in this option, you would need a CASE for every value.

-- CASE QUERY
DO $$
DECLARE
-- control var
    var integer := 1;
-- result vars
    var1 integer := 1;
    var2 integer := 2;
    var3 integer := 3;
    var4 integer := 4;
-- final values
    val1 integer;
    val2 integer;
BEGIN
    SELECT
        CASE var -- CASE for val1
            WHEN 1 THEN var1
            WHEN 2 THEN var3 END,
        CASE var -- CASE for val2
            WHEN 1 THEN var2 
            WHEN 2 THEN var4 END
    INTO val1,val2;

    RAISE NOTICE '%',val1; -- outputs: 1
    RAISE NOTICE '%',val2; -- outputs: 2
END;$$

If var changes to '2', then the output would be 3 and 4.

Here you change your code

CASE var
    WHEN 1 THEN
        variable1, variable2
    WHEN 2 THEN
        variable3, variable4

to

CASE var -- CASE for val1
    WHEN 1 THEN variable1
    WHEN 2 THEN variable3 END,
CASE var -- CASE for val2
    WHEN 1 THEN variable2
    WHEN 2 THEN variable4 END

If you add more variables, you add more cases.

SECOND OPTION: JSONB usage

This option is the best approach since you don't have to code to many CASE clauses, and you don't have to create extra steps to the process.

Basically, you use a JSONB variable that has all the variables you need like this:

{
   "val1": 1,
   "val2": 2
}

And here is how you do it:

-- USING JSONB
DO $$
DECLARE
-- control var
    var integer := 1;
-- result vars
    var1 integer := 1;
    var2 integer := 2;
    var3 integer := 3;
    var4 integer := 4;
-- JSON var that will have val1 and val2 data
    jsonvar jsonb;
BEGIN
    SELECT CASE var 
            WHEN 1 THEN CAST('{"val1":'||var1||',"val2":'||var2||'}' as jsonb)
            WHEN 2 THEN CAST('{"val1":'||var3||',"val2":'||var4||'}' as jsonb) END
    INTO jsonvar;

    RAISE NOTICE '%',jsonvar->>'val1'; -- outputs: 1
    RAISE NOTICE '%',jsonvar->>'val2'; -- outputs: 2
END;$$

Note that here you insert a JSON object into a variable that is of type JSONB. Same quantity of vars into vals.

Since JSON is really a flexible object, to add more variables you just have to tweak your JSON accordingly.

In PostgreSQL is best to use JSONB instead of plain JSON. The manual states:

The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

THIRD OPTION: TYPE usage

This option is the more complex option because we enter in the RECORD terrain. Yes, (data1,data2) in PostgreSQL is a RECORD. What is a record? in simple words, is a ROW that doesn't have a data structure.

What does RECORD mean? Well, to have it clear, when you create a table, for example:

CREATE TABLE data(val1 integer,val2 integer);

If you want to insert data into the table "data" you have to insert a record, so when you do:

INSERT INTO data(val1,val2) VALUES (1,2);

Your inserting a RECORD (1,2) into data.

NOTE that 1 and 2 are inside parentheses (1,2). So, when you write (variable1,variable2) your making a RECORD that contains variable1 and variable2.

Acording to the manual

Record variables are similar to row-type variables, but they have no predefined structure...

RECORD is not a true data type, only a placeholder.

Here is the problem. A RECORD does not have a structure, so PostgreSQL does not know how to parse it. When you use INSERT INTO your telling PostgreSQL the structure, so the record takes the structure of the table (previous example).

When you do:

SELECT
    CASE var
    WHEN 1 THEN
        (variable1, variable2)
    WHEN 2 THEN
        (variable3, variable4)

Your selecting a RECORD.

To simplify it, if you do SELECT (1,2) you get:

row (record)
------------
(1,2)

How do you assign a structure to a ROW? well, you use SELECT, FOR, or you use TYPE. SELECT and FOR are used similar to INSERT INTO, you know the data structure. In this case, there is no reference so using TYPE is mandatory.

In PostgreSQL you can create you personal data types. Therefore, you can do:

CREATE TYPE mytype AS (val1 integer, val2 integer);-- execute only once

Similar to a table, you can create a TYPE once, and the TYPE gets available in the whole database. To drop the type you just do DROP TYPE mytype;

mytype has the two values needed for the variables, like a table, TYPE can have any "columns" with any data types.

Now, if you do SELECT (1,2)::mytype you get:

row (record)
------------
(1,2)

Still a row, because PostgreSQL does not know how to parse it.

But if you do SELECT * FROM (VALUES(1,2)) AS mytype(val1,val2); you get

 val1 | val2
------+------
    1 |    2

This is because you are telling PostgreSQL how to parse it (Note the use of VALUES).

This shows that is not that easy to assign the structure to a record row. But is possible as shown below:

-- USING TYPES
-- Requires mytype created
DO $$
DECLARE
-- control var
    var integer := 1;
-- result vars
    var1 integer := 1;
    var2 integer := 2;
    var3 integer := 3;
    var4 integer := 4;
-- final values
    val1 integer;
    val2 integer;
BEGIN
    SELECT x[1].val1,x[1].val2
    FROM(
        SELECT ARRAY(
            SELECT CASE var
                WHEN 1 THEN (var1,var2)::mytype 
                WHEN 2 THEN (var3,var4)::mytype END
        )::mytype[] AS x
    )dataset
    INTO val1,val2;

    RAISE NOTICE '%',val1; -- outputs: 1
    RAISE NOTICE '%',val2; -- outputs: 2
END;$$

The key part is using ARRAY.

When you execute:

SELECT x.val1,x.val2
        FROM(
            SELECT CASE 1
                WHEN 1 THEN (1,2)::mytype
                WHEN 2 THEN (3,4)::mytype END AS X
        )dataset

You get this error:

ERROR:  missing FROM-clause entry for table "x"

PostgreSQL doesn't know how to parse it, so you tell PostgreSQL to get it through an ARRAY.

SELECT
x[1].val1,x[1].val2
FROM(
    SELECT ARRAY(
        SELECT CASE 1
           WHEN 1 THEN (1,2)::mytype
           WHEN 2 THEN (4,5)::mytype END
    )::mytype[] AS x
) dataset

This outputs:

 val1 | val2
------+------
    1 |    2

The problem with this option is that the TYPE you create is static, so if you have to change it you would have to drop the type and create it again.

But that is how you do it. The second option is the best, also, a more modern aproach.

Collectives™ on Stack Overflow

How do i SELECT multiple Values with postgreSQL INTO multiple variables Depending on a CASE?

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