Return BIGINT[] FROM postgresql function

Question

I know that some similar questions have been asked before but none of the answers worked for me! I use POSTGRESQL 8.4 and am trying to return an array of BIGINT values from a function. My query looks like:

CREATE OR REPLACE FUNCTION public.bigint_func(
    in "in_arg" BIGINT)
  RETURNS SETOF BIGINT
  AS
$body$
DECLARE bigint_list BIGINT [ ];
BEGIN
      SELECT
        id
      FROM
        table1
    INTO bigint_list;
  RETURN NEXT;
  END
$body$
  LANGUAGE 'plpgsql'
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER;

and I'd like to use that function as below:

SELECT * 
FROM 
table1 
JOIN (SELECT ids
  FROM bigint_func(123))t2 ON table1.id = t2.id

but I get the following error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

How should I write the code for the function?

Hambone · Accepted Answer · 2019-01-14 23:49:57Z

2

Looks like you might be blending two ways of doing what you are trying to accomplish. Either loop through and return a value at a time, or return everything as a query.

I think either of these will work.

Loop and return a row at a time.

CREATE OR REPLACE FUNCTION bigint_func(
    in "in_arg" BIGINT)
  RETURNS SETOF BIGINT
  AS
$body$
DECLARE
 bigint_list BIGINT [ ];
 my_id bigint;
BEGIN
  for my_id in
      SELECT
        id
      FROM
        table1 
  loop
    RETURN NEXT my_id;
  END loop;
end;
$body$
  LANGUAGE 'plpgsql'
  VOLATILE

Or return the entire query as a dataset.

CREATE OR REPLACE FUNCTION bigint_func(
    in "in_arg" BIGINT)
  RETURNS SETOF BIGINT
  AS
$body$
DECLARE
 bigint_list BIGINT [ ];
BEGIN
  return query
      SELECT
        id
      FROM
        table1;
end;
$body$
  LANGUAGE 'plpgsql'
  VOLATILE

-- EDIT --

To get the query working, maybe change the return type to a table:

CREATE OR REPLACE FUNCTION bigint_func(in "in_arg" BIGINT)
RETURNS table (id bigint)
  AS
$body$
BEGIN
  return query
    SELECT
      table1.id
    FROM
      table1;
end;
$body$
  LANGUAGE 'plpgsql'
  VOLATILE;

Then you can name the field anything you want (id in this case)

SELECT * 
FROM 
  table1 
  join bigint_func(123) t2
    ON table1.id = t2.id

edited Jan 14, 2019 at 23:49

answered Jan 14, 2019 at 23:24

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

2 Comments

Ms workaholic Over a year ago

The function is fine now but I'm having problem with joining; when I use this query SELECT * FROM table1 JOIN (SELECT id FROM bigint_func(123))t2 ON table1.id = t2.id I get the error that t2.id is not known!

Hambone Over a year ago

Hmm... I see what you mean. Maybe there is a way to alias the output, but if not, you can declare the return type as a table (or setof)

Collectives™ on Stack Overflow

Return BIGINT[] FROM postgresql function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related