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

[Shree111]

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

[rski]

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

You mean any any procedure?

[Shree111]

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....

[rski]

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

[Shree111]

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.....