::Pipelined in Oracle as well in PostgreSQL::

Pipelined Table Functions:- [ORACLE]
===========================

If you want to return multiple rows to the calling environment, then piplined table functions is prefred. It will increase the dbperformance as well.

Ex:-

Step 1:
-----------
CREATE TABLE EMP(EMPNO INT,ENAME VARCHAR2(10),SAL INT);

Step 2:
-----------
Insert sample data.

Step 3:
-----------
Create an object for the row type casting.
CREATE OR REPLACE TYPE emp_row
AS OBJECT
(
empno INT,
ename VARCHAR2(20),
SAL INT
);

Step 4:
-----------
Create a Return Type for the pipelined function.
CREATE OR REPLACE TYPE emp_table_type
AS TABLE OF emp_row;

Step 5:
-----------
CREATE OR REPLACE FUNCTION emp_pipe_function RETURN emp_table_type PIPELINED IS
BEGIN
FOR rec in (select * from emp)
LOOP
PIPE ROW (emp_row(rec.empno,rec.ename,rec.sal));
END LOOP;
RETURN;
END;


Step 6:
----------
SQL> select * from table(emp_pipe_function);

EMPNO ENAME SAL
---------- -------------------- ----------
1 Dinesh 1000
2 MANOJA 2000
3 SREE VANI 3000
4 NIVAS 4000

SQL> select * from emp;

EMPNO ENAME SAL
---------- ---------- ----------
1 Dinesh 1000
2 MANOJA 2000
3 SREE VANI 3000
4 NIVAS 4000

Same Case In PostgreSQL
=========================

Step 1:
---------
CREATE TABLE EMP(ENO INT,ENAME VARCHAR,SAL INT);

Step 2:
---------
Insert Sample Rows

Step 3:
---------
CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF PUBLIC.EMP
AS
$$
SELECT * FROM EMP;
$$
LANGUAGE SQL;

(OR)

CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF PUBLIC.EMP
AS
$$
BEGIN
RETURN QUERY SELECT * FROM EMP;
END;
$$
LANGUAGE PLPGSQL;

(OR)

CREATE OR REPLACE FUNCTION EMP_FUNC() RETURNS SETOF PUBLIC.EMP AS
$$
DELCARE
T RECORD;
BEGIN
FOR  T IN SELECT * FROM EMP
LOOP
RETURN NEXT T;
END LOOP;
END;
$$
LANGUAGE PLPGSQL;

Step 4:
----------
postgres=# SELECT * FROM EMP_FUNC();
eno | ename | sal
-----+-----------+------
1 | Dinesh | 1000
2 | MANOJA | 2000
3 | SREE VANI | 3000
4 | NIVAS | 4000
(4 rows)

postgres=# SELECT * FROM EMP;
eno | ename | sal
-----+-----------+------
1 | Dinesh | 1000
2 | MANOJA | 2000
3 | SREE VANI | 3000
4 | NIVAS | 4000
(4 rows)