Postgres Stored procedure in select statement cannot get the datas

Question

I am trying to get the data from the Database use function of select prison();.but i got error .Please advise me.

 CREATE OR REPLACE FUNCTION prison() RETURNS refcursor AS $$
 DECLARE
    ref refcursor;
       BEGIN
          OPEN ref FOR SELECT  round,ben_sc,ben_st FROM prison_issue;

          RETURN ref;
       END;
$$ LANGUAGE plpgsql;

and calling like this

     select prison();

also i tried.but cannot executed the rows.

       BEGIN;

      SELECT prison();
      -- Returns: <unnamed portal 2>

      FETCH ALL IN "<unnamed portal 24>";
      COMMIT;

If you want to get data from table you can use "RETURN QUERY" — Akhilesh
– Akhilesh, Commented Sep 23, 2015 at 11:25

user330315 · Accepted Answer · 2015-09-23 11:44:43Z

1

There is no need for a PL/pgSQL function for this:

CREATE OR REPLACE FUNCTION prison() 
   RETURNS setof prison_issue 
AS $$
  SELECT * FROM prison_issue;
$$ LANGUAGE sql;

You also need to use:

select * from prison();

to retrieve the data, do not use select prison() (which only returns a single record, not multiple rows)

You didn't show us your definition of the table prison_issue if you don't want to return all columns you need something like this:

CREATE OR REPLACE FUNCTION prison() 
   RETURNS table (round integer, ben_sc text, ben_st text)
AS $$
  SELECT SELECT  round,ben_sc,ben_st FROM prison_issue;
$$ LANGUAGE sql;

You will need to adjust the part table (round integer, ben_sc text, ben_st text) to match the data type of the columns you select.

answered Sep 23, 2015 at 11:44

user330315

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

Comments

Akhilesh · Accepted Answer · 2015-09-23 11:39:53Z

0

Below is an example code.I have assumed the data types.Replace with the real ones.

CREATE OR REPLACE FUNCTION prison() RETURNS TABLE(round numeric,ben_sc character varying,ben_st character varying) AS $$

       BEGIN

          RETURN QUERY SELECT  p.round,p.ben_sc,p.ben_st FROM prison_issue p;

       END;
$$ LANGUAGE plpgsql;

edited Sep 23, 2015 at 11:39

answered Sep 23, 2015 at 11:31

Akhilesh

1,3135 gold badges22 silver badges58 bronze badges

8 Comments

sarath jay Over a year ago

I got error...ERROR: column reference "round" is ambiguous LINE 1: SELECT round,ben_sc,ben_st FROM prison_issue ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: SELECT round,ben_sc,ben_st FROM prison_issue CONTEXT: PL/pgSQL function prison_issue() line 5 at RETURN QUERY ********** Error **********

Akhilesh Over a year ago

@sarath jay Updated the answer.

sarath jay Over a year ago

CREATE OR REPLACE FUNCTION prison_issue() RETURNS TABLE(round text,ben_sc integer,ben_st integer) AS $$ BEGIN RETURN QUERY SELECT round,ben_sc,ben_st FROM prison_issue; END; $$ LANGUAGE plpgsql;

Akhilesh Over a year ago

@sarath jay Use alias "p.". I have updated the answer

sarath jay Over a year ago

boss it's working....the output is (r1,1,1)<br/>(r1,1,2)<br/>(r2,1,3)...but cannot come with seperate column why??

|

Collectives™ on Stack Overflow

Postgres Stored procedure in select statement cannot get the datas

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related