By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

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

P: 10
Can you use a stored procedure in a subquery in postgres?
Dec 4 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 700
Can you use a stored procedure in a subquery in postgres?
You mean any any procedure?
Dec 4 '07 #2

P: 10
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

Expert 100+
P: 700
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

P: 10
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.