How to RETURN an UPDATE from SELECT in Postgree within a Function?

Asked

Viewed 98 times

0

I have a "FUNCTION RETURNS TABLE()" in Postgree, where I select from select, but I need to apply an update from select, but I cannot return to the table. I tried it this way and I didn’t succeed:

CREATE OR REPLACE FUNCTION schema.funcão()
RETURNS TABLE(valor1 TIMESTAMP, valor2 BIGINT, valor3 NUMERIC, valor4 INT) AS $$
BEGIN
    RETURN QUERY sub2
    UPDATE schema.table
    SET valorBOLEANO = true
    FROM (
    SELECT 
            date_trunc(sub1.valor1),
            count(1),
            valor3,
            sub1.valor4,
        FROM(SELECT ... ) AS sub1
    GROUP BY 1,sub1.valor4) AS sub2
END $$
LANGUAGE plpgsql;
sql postgresql
  • 1

    First of all the DBMS name is Postgresql, but usually called postgres. I don’t understand what you want with this SELECT if you will just make SET valueBOLEANO = true. Do you just want to test if SELECT produces any results? If it is, use the EXISTS clause. Another thing that doesn’t make sense is you put some instruction after the RETURN.

  • Hello anonimo, thank you for answering! Actually the DBMS I am using is Navicat Premium, with the language for Postresql database.

  • This Select I’m doing, it calculates all my business rule by returning me only the values "treated" for me to search with Select from (Select... business rule happens here) and at the end of Funcion RETURN QUERY returns these values to the temporary Table I am creating, so I can use this data in my backend code.

  • However I need to make a change, where the answer of this treated select, change my valueBolean to "true", and after that return the same values that previously returns, The question is that instead of returning the data from select it is returning the update logically because it is after Return, so the question is, how to return these select by doing the update? Thanks in advance.

  • Check that the RETURNING clause of the UPDATE command meets your needs, needs that are not clear to me yet.

  • Okay, I’m doing an UPDATE SET valueBolean = true from ( Select ...) But I need to return the values of the Same SELECT I’m using inside the UPDATE ex FROM:

  • Current UPDATE SET = true (SELECT id FROM user = 1) RETURN (Select value)

  • I was able to express myself better?

  • Why don’t you declare a record type with the variables you want to return, do the SELECT and save it in the record type, check if everything went well and, if so, update your table and return the record type, in case of error decide what to do. Read the Postgresql Plpgsql documentation.

  • Let’s go continue this discussion in chat.

Show 5 more comments
No answers

Browser other questions tagged sql postgresql

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