Returning an array of a function in Postgresql

Asked

Viewed 733 times

0

I’m having a question here that I need to solve with a postgresql database function.

The problem is this, I need to cross a table that’s called stocking with a view called stockpile. In this table stocking are only the codes of the records that had their stock updated, the fields are id_general, command and dt_atz, the view stockpile back all stock table records as per your affiliate and etc.

If I cross the two, comparing the general id_s of both the query takes considerably to bring the records, even if they are few (it takes about 50 seconds to bring 4 records).

So I need to first select all the table records stocking and after that check all records in the view stockpile that are in agreement with what resulted in the first query. Something like what is below:

SELECT * FROM estoque WHERE id_geral IN (001,002,003)

This must be returned along with the value of the table command field stocking.

There is a way for me to do this within a database function so that it is possible for me to just call the function and it returns me all this information?

postgresql query function

  • And the array enters where in this problem or solution?

  • It is that I need to make two queries within the function and go through the result of the first in the second... so I know which record of the general id_i will put in clause IN() of the second query.

  • You can enter the function code?

  • There it is, as I do not work directly with bank I have no idea how to set up this function... That’s why I needed help on how to get to this solution.

1 answer

0


I’m not sure I understand what you need, but look below

Here I created a function that returns user ID greater than 1 and less than 30

CREATE FUNCTION ids_para_where(@id int)
returns table
as
RETURN (SELECT USU_CODIGO
        FROM  USUARIO
        WHERE USU_CODIGO >= 1 AND USU_CODIGO <=30)

below I make select by passing on Where the function result

SELECT * FROM USUARIO WHERE USU_CODIGO IN (SELECT * FROM ids_para_where (1))

see if this helps

  • Solved the problem, although the performance is still questionable... But thanks for the help!

Browser other questions tagged postgresql query function

You are not signed in. Login or sign up in order to post.