CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

How to run function on query results?

I have a query:

select a,b,c,d
from x
join y using (id)
join z using (id2)
where....

this query gives many rows. I would like to run func2(a,b,c,d) on each row from the query.

I tried:

with B as (
    select a,b,c,d
    from x
    join y using (id)
    join z using (id2)
    where....)
select func2(a,b,c,d)
from B

but it doesn't work. I'm not sure WITH is the right solution.

Upvotes: 1

Views: 856

Answers (2)

Vivek S.
Vivek S.

Reputation: 21945

A function can be use within select queries

for example

create table t(a int,b int);
insert into t select generate_series(1,5), generate_series(5,5);

and If you've a function like below

create function fn(val int,val1 int) returns integer
as
$$
select $1+$2;
$$
language sql

usage:

select fn(1,2)

Result:

fn 
-- 
3  

(1 row(s) affected)

the same way you can use it with the select query

select a,b,fn(a,b) 
from t

Result:

a b fn 
- - -- 
1 5 6  
2 5 7  
3 5 8  
4 5 9  
5 5 10 

(5 row(s) affected)

on the other hand your WITH query should work I guess the problem is something else

See this demo

Upvotes: 1

Kapila Ramji
Kapila Ramji

Reputation: 104

You can simply run the function within the select. This will loop for every row returned. Try it in the following manner:

select func2(a,b,c,d)
from x
join y using (id)
join z using (id2)
where....

Upvotes: 4

Related Questions