referencing parameters on stored procedures in PostgreSQL

Question

I'm Converting some SQLServer stored procedures and I have a problem that I can't solve. That's SQLServer function:

CREATE FUNCTION [dbo].[getViewNodeHierarchyAux](@pivot varchar(255), @parents varchar(max))
RETURNS @view TABLE (PARENT_OID varchar(255), CHILD_OID varchar(255))
...
insert into @view select F.* from BTREENODES_NODEHIERARCHY T cross apply [dbo].getViewNodeHierarchyAux(T.CHILD_OID,@parents) F where T.PARENT_OID=@pivot;

And that's the conversion I have thought in PostgreSQL:

CREATE OR REPLACE FUNCTION getviewnodehierarchyauxprueba(IN pivot character varying, IN parents character varying)
...
  RETURNS TABLE(test_parent_oid character varying, test_child_oid character varying)
return query (select F.* from BTREENODES_NODEHIERARCHY T cross join getviewnodehierarchyprueba(T.CHILD_OID,parents) F WHERE T.PARENT_OID=pivot);

PgAdmin tells me that there's no valid reference to table 't' in 'from' clause. And if I write it this way getviewnodehierarchyprueba((select CHILD_OID from BTREENODES_NODEHIERARCHY),parents)

It returns more than one record and it doesn't work. Any ideas? thank you!

As *always*: your version of PostgreSQL? Also, describe what the function *should* return. One row or several? — Erwin Brandstetter, Dec 10 '12 at 11:14
Closely related: http://stackoverflow.com/questions/11472790/analogue-to-cross-apply — Erwin Brandstetter, Dec 10 '12 at 11:25

score 2 · Answer 1 · answered Dec 10 '12 at 11:22

PostgreSQL doesn't allow use a value of joined relation as parameter for second relation in joining. It will be available in 9.3 with LATERAL subselect. It is a first issue. Second issue - subselect can return only one row

(select CHILD_OID from BTREENODES_NODEHIERARCHY)

probably returns more than one row

You don't need wait to 9.3 - use a CTE (Common Table Expressions) instead for processing some recursive data.

referencing parameters on stored procedures in PostgreSQL

1 Answers1