Combining multiple SELECT INTO results into one variable

Question

The method I am using may not be efficient (or possible), if so please let me know. I am trying to use SELECT INTO to select two values and then attach them to a variable (v1) which will be returned by the function as one cell. Inserting UNION ALL between the two SELECT INTO statements results in an ERROR: syntax error at or near "UNION"

EDIT - the function provides two unique values (not null) which are specified for these two SELECT INTO statements

desired output:

    v1             v2      v3    etc.
2678, 2987

excerpt from function:

SELECT value
    INTO v1
    FROM table
    WHERE year <= parameteryear;

SELECT value
    INTO v1
    FROM table    
    WHERE yearinteger >= parameteryear;

data := v1;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

In Postgres, select into is used to create tables, not to assign values to variables. I am unclear on what you are trying to accomplish. — Gordon Linoff
– Gordon Linoff, Commented Jul 31, 2014 at 18:06
@GordonLinoffin PL/pgSQL select .. into is definitely used to put values into variables. postgresql.org/docs/current/static/… — user330315
– user330315, Commented Jul 31, 2014 at 18:11
you want v1 to be a string with two numbers and a comma between them? — Hogan
– Hogan, Commented Jul 31, 2014 at 18:18
The function header is missing, but it is an integral, essential part. Among other things we need to see the actual type of v1, as well as all parameters and the declared RETURN type. Also include a proper description of what you are trying to achieve with the function. To be certain we also need a table definition (\d tbl in psql). Can value be NULL in the table? — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 1, 2014 at 15:16

Hogan · Accepted Answer · 2014-08-01 17:31:18Z

1

 SELECT cast(t1.value as varchar(20)) || ', ' || cast(t2.value as varchar(20))
     INTO v1
     FROM table as t1, table as t2
     WHERE t1.year <= parameteryear
       AND t2.yearinteger >= parameteryear;

edited Aug 1, 2014 at 17:31

answered Jul 31, 2014 at 18:23

Hogan

70.7k10 gold badges82 silver badges119 bronze badges

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

6 Comments

user330315 Over a year ago

+ is not used to concatenate strings in SQL

interpost Over a year ago

Thanks, that is much more compact, but I receive an ERROR: operator does not exist: character varying + unknown

Hogan Over a year ago

@interpost - Sorry wrong platform. see above -- you want || not +

Hogan Over a year ago

@a_horse_with_no_name - it is in some (well SQL Server for one)

user330315 Over a year ago

The SQL standard defined back in 86 || as the string concatenation character and Postgres complies with the standard.

|

Dan Bracuk · Accepted Answer · 2014-07-31 18:09:24Z

1

If you want to use a union all query, it must be in a derived table.

select value
into v1
from (union query goes here) derivedTable

answered Jul 31, 2014 at 18:09

Dan Bracuk

20.8k5 gold badges29 silver badges44 bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2016-09-08 14:05:06Z

Each individual statement is dubious, because SELECT INTO expects a single row to be returned, but your WHERE condition does not seem to guarantee that at all.

Combining both in a single statement is even more dubious, because you have all kinds of side effects if one of both is missing or NULL or not unique. Only makes sense if none of those complications can happen. I seriously doubt that.

If you can at least guarantee that you get values for both conditions, this would work:

SELECT concat_ws(',', t1.value, t2.value) 
INTO   v1
FROM   tbl t1, tbl t2
WHERE  t1.year <= parameteryear
AND    t2.yearinteger >= parameteryear;

But I am almost positive, it doesn't do what you want.
Also, judging from your "desired output" you may actually want a set of rows.

In short: The fragment you presented does not make any sense at all. Post your complete code and what you actually trying to achieve. Most certainly, there is a different approach.

Yes, I realized after receiving Hogan's answer that I had oversimplified the code I posted. Posting of the entire function would probably be excessive, but to add the missing context as you mentioned, there is a single row that is not null specified for these two SELECT INTO statements.
@interpost: Even then, there is probably a better solution. But let's leave this question at that.

Collectives™ on Stack Overflow

Combining multiple SELECT INTO results into one variable

3 Answers 3

6 Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Related