469,126 Members | 1,295 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Can you use a stored procedure in a subquery in postgres?

Can you use a stored procedure in a subquery in postgres?
Dec 4 '07 #1
4 4897
rski
700 Expert 512MB
Can you use a stored procedure in a subquery in postgres?
You mean any any procedure?
Dec 4 '07 #2
You mean any any procedure?

CREATE OR REPLACE FUNCTION testobjectfortotal()
RETURNS SETOF total_type1d AS $$

begin

select (select account_id from tbl_ecpaccount where ecpuser_key =a.ecpuser_key) as Account_id, a.id,
(select distinct ecppayer_type from tbl_ecppayer where ecppayer_id = a.ecpclaim_carrier_id limit 1) as Payer_type,
(select * from zzz) as Payertype,
count(a.ecpclaim_carrier_id),
(select * from xxx) as ecpsubmitted
from tbl_ecpclaim a group by ecpclaim_carrier_id, ecpuser_key having ecpclaim_carrier_id in
(select distinct ecpclaim_carrier_id from tbl_ecpclaim);

return total_type1d;


end;

$$ LANGUAGE 'plpgsql';

i have written procedure like this but its showing error as following.......



ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "total_type1d"

here "total_type1d" is type which contains userdefined types....
Dec 5 '07 #3
rski
700 Expert 512MB
CREATE OR REPLACE FUNCTION testobjectfortotal()
RETURNS SETOF total_type1d AS $$

begin

select (select account_id from tbl_ecpaccount where ecpuser_key =a.ecpuser_key) as Account_id, a.id,
(select distinct ecppayer_type from tbl_ecppayer where ecppayer_id = a.ecpclaim_carrier_id limit 1) as Payer_type,
(select * from zzz) as Payertype,
count(a.ecpclaim_carrier_id),
(select * from xxx) as ecpsubmitted
from tbl_ecpclaim a group by ecpclaim_carrier_id, ecpuser_key having ecpclaim_carrier_id in
(select distinct ecpclaim_carrier_id from tbl_ecpclaim);

return total_type1d;


end;

$$ LANGUAGE 'plpgsql';

i have written procedure like this but its showing error as following.......



ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "total_type1d"

here "total_type1d" is type which contains userdefined types....

What do you wanr to rerutn, the whoel query you wrote. If co you have to use loops iterating select query. And use 'return next'. See more on postgresql.org in plplgsql section
Dec 6 '07 #4
What do you wanr to rerutn, the whoel query you wrote. If co you have to use loops iterating select query. And use 'return next'. See more on postgresql.org in plplgsql section

yeah...thanks...i have got the result.....
Dec 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Jarrod Morrison | last post: by
3 posts views Thread by Amy | last post: by
5 posts views Thread by bbawa1 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.