Returning an array from a function in PostgreSQL

Question

Returning an array from a function in PostgreSQL

Navigation

#1 by (0 votes)

0

I have a question here that I need to troubleshoot with a function in the postgreSQL database.

The problem is this, I need to cross a table called stock_alt with a view called stock . In this table inventory_alt are only the codes of the records that had their inventory updated, the fields are general_id, command and dt_atz, since the inventory your affiliate and etc.

If I cross the two, comparing the general id of both the query takes a long time to bring the records, even if they are few (it takes around 50 seconds to bring 4 records).

Then I need to first select all the records in the stock_alt table and then check all the records in the stock view that match the result of the first query. Something like the one below:

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

This should be returned along with the value of the command field of the stock_alt table.

Is there a way I can do this within a database function so that I can just call the function and it returns all of this information to me?

query postgresql

asked by anonymous 01.03.2016 / 15:07

1 answer

Click Framelayout does not work with Listview inside Modal interfering with page scrolling

score 0 · Accepted Answer

I do not know if I understood correctly what you need, but see below

Here I created a function that returns the user ids 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 the select by passing in the where the result of the function

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

see if this helps