New answers tagged

3

I think you need: for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop If a table has more than one constraints, your code will try to recreate each one of them many times. Or just use a single for loop, I don't think you need the nesting. --Recreate not_deferrable_constraints for add_constraint in (select constraint_name, ...


0

A janky solution until something better comes along. Use it like this: if you mean updated=updated, then you write updated=NULL. create temp table test ( created TIMESTAMPTZ NOT NULL DEFAULT now(), updated TIMESTAMPTZ ); CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN IF NEW.updated IS NULL THEN IF OLD....


0

OLD contains the current values in the table, NEW the values like they would be after the UPDATE. If NEW.updated IS DISTINCT FROM OLD.updated, it is clear that updated must have been changed by the UPDATE statement (or a previous trigger). If NEW.updated IS NOT DISTINCT FROM OLD.updated, you cannot know if updated was specified in the UPDATE statement or ...


0

No way to determine does some field was set in UPDATE query text. You may operate with NEW.updated and OLD.updated values only. Actions table: OLD.updated NEW.updated Action ------------------------------------ NULL NULL nothing NULL now() nothing NULL NOT now() nothing now() NULL ...


3

You don't really need a function for that, you can use conditional aggregation: SELECT "user", count(*) filter (where event = 'start') AS start, count(*) filter (where event = 'end') AS end FROM stats GROUP BY "user"; You can pass a complete row to a function when you declare a parameter with the type of the table: CREATE OR REPLACE ...


0

Seems to me that your function could be designed as a scalar function that receives two parameters and returns an integer. Then your query applies that function over the rows and sums the results. But you could probably do the whole thing in a single query if you wanted to with a common table expression.


1

Substrings first argument is the offset, the second argument is the number of characters from the offset. You need to subtract the offset from the second parameter: SELECT title , SUBSTRING(`content` -- offset in string ,LOCATE('[flv:',`content`) -- number of characters from offset ,LOCATE(...


1

I checked the documentation again, and the answer to my second question is, yes you can alter it using the alter function query. alter function my_function() stable; Also, I see that checking for function volatility has been asked and answered before. https://stackoverflow.com/questions/54452415/how-to-check-whether-a-function-is-immutable


Top 50 recent answers are included