471,893 Members | 1,795 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

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 5114
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
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.