CODEWITHSUNDEEP
sqlpostgresqltypes
Xbreizh
Xbreizh

Reputation: 373

How to return type table in postgres?

I create a type then used it to create a table such as:

CREATE TYPE PARTOK AS (col1 VARCHAR(58), col2 VARCHAR(58),  expiration VARCHAR(58), last_update BIGINT, date TIMESTAMP);
CREATE TABLE PARTOK_JOB OF PARTOK;

Then I inserted a few lines:

INSERT INTO PARTOK_JOB VALUES ('one', 'test1','nothing',  0, null);
INSERT INTO PARTOK_JOB VALUES ('one', 'test2','nothing',  0, null);
INSERT INTO PARTOK_JOB VALUES ('one', 'test3','nothing',  0, null);
INSERT INTO PARTOK_JOB VALUES ('two', 'test3','nothing',  0, null);

I created a simple function to return a table of that type:

CREATE OR REPLACE FUNCTION get_partok(
in_col1 VARCHAR(40)
)
RETURNS PARTOK_JOB

LANGUAGE sql
AS
$$
SELECT * FROM partok_job WHERE col1 = in_col1 ;
$$
;

then used the following to call it:

SELECT * FROM get_partok('one');

The issue is that it returns a single line. How could I make it return all the related records? Is there a way to do it using that type table or do I have to define each column of the return table?

Thx

Upvotes: 1

Views: 2285

Answers (1)

Ftisiot
Ftisiot

Reputation: 1868

As per postgres documentation

SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. In the simple (non-set) case, the first row of the last query's result will be returned. (Bear in mind that “the first row” of a multirow result is not well-defined unless you use ORDER BY.) If the last query happens to return no rows at all, the null value will be returned.

Alternatively, an SQL function can be declared to return a set (that is, multiple rows) by specifying the function's return type as SETOF sometype, or equivalently by declaring it as RETURNS TABLE(columns). In this case all rows of the last query's result are returned. Further details appear below.

You'll need to use set_of. just substitute in your code

RETURNS PARTOK_JOB

with

RETURNS setof PARTOK_JOB

The whole function definition should be

CREATE OR REPLACE FUNCTION get_partok(
in_col1 VARCHAR(40)
)
RETURNS setof PARTOK_JOB

LANGUAGE sql
AS
$$
SELECT * FROM partok_job WHERE col1 = in_col1 ;
$$
;

after making this change, when selecting

SELECT * FROM get_partok('one');

the output is

 col1 | col2  | expiration | last_update | date 
------+-------+------------+-------------+------
 one  | test1 | nothing    |           0 | 
 one  | test2 | nothing    |           0 | 
 one  | test3 | nothing    |           0 | 
(3 rows)

Upvotes: 2

Related Questions