Binding an Array in SQL

Question

Is it possible to bind an array to a name in a SQL statement? I'm looking for something similar to a Lisp/Haskell let-expression.

My first thought was to use a 'with' clause before the select statement that uses the array in a function call.

WITH thenumbers AS ARRAY(SELECT buildingnum FROM numbers WHERE area = 'Bu',
                     SELECT buildingnum FROM numbers WHERE area = 'Ba',
                     SELECT buildingnum FROM numbers WHERE area = 'E',
                     SELECT buildingnum FROM numbers WHERE area = 'O',
                     SELECT buildingnum FROM numbers WHERE area = 'Mi',
                     SELECT buildingnum FROM numbers WHERE area = 'Ho',
                     SELECT buildingnum FROM numbers WHERE area = 'Wa',
                     SELECT buildingnum FROM numbers WHERE area = 'Wi')
SELECT bnorm(area,preal,thenumbers) FROM electrical_energy;

Two details that might inform an answer are that I'm working with Postgres 8.3 and 'numbers' is a view rather than a table.

user330315 · Accepted Answer · 2014-03-08 19:43:28Z

1

Something like this might work:

SELECT bnorm( area, 
              preal, 
              (select array_agg(buildingnum) 
               from numbers 
               where area in ('Bu','Ba','E','O','Mi','Ho','Wa','Wi') 
             ) 
FROM electrical_energy;

8.3 is pretty old and no longer supported you should really plan an upgrade now.

answered Mar 8, 2014 at 19:43

user330315

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

Collectives™ on Stack Overflow

Binding an Array in SQL

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