Postgresql stored function returning only values not with column names

Question

I am new in Postgresql. I just created function with composite type.

The following is my code:

create type search_type as (
result_id bigint, 
result_name character varying,
result_loc character varying,
result_type character varying,
result_pic character varying
);

    CREATE OR REPLACE FUNCTION search_result("@search_text" character varying)
  RETURNS SETOF search_type AS
$BODY$
DECLARE r search_type%rowtype;
BEGIN   
    FOR r IN SELECT "User_id" AS result_id, CONCAT("User_firstname", ' ', "User_lastname") AS result_name, "User_loc" AS result_loc, "User_picurl" AS result_pic, 'user' AS result_type FROM users_list WHERE CONCAT("User_firstname", ' ', "User_lastname") LIKE CONCAT("@search_text", '%') LOOP
        RETURN NEXT r;
    END LOOP;
    FOR r IN SELECT "Community_id" AS result_id, "Community_name" AS result_name, "Community_location" AS result_loc, "Community_img_url" AS result_pic, 'community' AS result_type FROM community_list WHERE "Community_name" LIKE CONCAT("@search_text", '%') AND "Type" = 'Created' LOOP
        RETURN NEXT r;
    END LOOP;
    RETURN;
END
$BODY$
  LANGUAGE plpgsql

But when I called the stored function like the below code, it will only datas not with column name.

SELECT search_result('c');

What will be the reason?

How can i return the column name with values? enter image description here

The above is the recorsd returned.

But i need to get the result as :

result_id  result_name  result_loc      result_pic  result_type
---------  -----------  ----------      -----------  ----------
13         test1        San Francisco                user
14         test2        San Francisco                user
15         test3        San Francisco                user
16         test4        San Francisco                user
17         test5        San Francisco                user

How can i get the result like above?

UPDATE

new code:

CREATE OR REPLACE FUNCTION search_result("@search_text" character varying, "@row_start" integer, "@row_end" integer)
  RETURNS SETOF search_type AS
$BODY$
DECLARE r search_type%rowtype;
BEGIN   
    FOR r IN SELECT "User_id" AS result_id, CONCAT("User_firstname", ' ', "User_lastname") AS result_name, "User_loc" AS result_loc, "User_picurl" AS result_pic, 'user' AS result_type FROM users_list WHERE CONCAT("User_firstname", ' ', "User_lastname") LIKE CONCAT("@search_text", '%') ORDER BY CONCAT("User_firstname", ' ', "User_lastname") ASC LIMIT "@row_end" OFFSET "@row_start" LOOP
        RETURN NEXT r;
    END LOOP;
    FOR r IN SELECT "Community_id" AS result_id, "Community_name" AS result_name, "Community_location" AS result_loc, "Community_img_url" AS result_pic, 'community' AS result_type FROM community_list WHERE "Community_name" LIKE CONCAT("@search_text", '%') AND "Type" = 'Created' ORDER BY "Community_name" ASC LIMIT "@row_end" OFFSET "@row_start" LOOP
        RETURN NEXT r;
    END LOOP;
    RETURN;
END
$BODY$
  LANGUAGE plpgsql

Then i called the procedure as :

SELECT * FROM search_result('t',3,0)

But no values returned.

@a_horse_with_no_name i just changed my question....

Kichu
– Kichu

2014-04-22 07:11:00 +00:00
Commented Apr 22, 2014 at 7:11 — Kichu
– Kichu, Commented Apr 22, 2014 at 7:11
ok i got the solution

Kichu
– Kichu

2014-04-22 08:56:51 +00:00
Commented Apr 22, 2014 at 8:56 — Kichu
– Kichu, Commented Apr 22, 2014 at 8:56

Craig Ringer · Accepted Answer · 2014-04-22 07:16:08Z

3

You need:

SELECT * FROM search_result('c');

not just:

SELECT search_result('c');

if you want a table result. Set-returning functions called in SELECT return composite tuples.

answered Apr 22, 2014 at 7:16

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kichu Over a year ago

i just need to limit the query in the stored function, so i chnaged the function...but now it will not return any values

Collectives™ on Stack Overflow

Postgresql stored function returning only values not with column names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related