New answers tagged

2

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there. Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard. So, to your actual question: it is not ...


2

It seems you just need the query: UPDATE sensors SET seconds=(SELECT sensors.starttime-"timestamp" FROM secure_sanity WHERE id=*id*) WHERE seconds=0; For that, it's overkill to write a plpgsql function. Or if you really want one, just replace this *id* by a function parameter put the above UPDATE query inside a CREATE FUNCTION / BEGIN ...


4

Problem You had to pick the spot where all possible complications come together. SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with EXECUTE for that. But the special plpgsql variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE. And it's further complicated by passing column names ...


2

In Postgres 8.4 or later you would use the USING clause of EXECUTE to pass values safely and efficiently. That's not available in your version 8.3, yet. In your version it could could work like this: CREATE OR REPLACE FUNCTION test_conditions() RETURNS SETOF bigint AS $func$ DECLARE _rec record; _func text; _result bigint; BEGIN FOR _func ...


3

Explanation: You declare user_info as record. The manual: Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. Bold emphasis mine. ...


2

if any errors, roll the whole thing back; It's simpler than you seem to think. Any exception in a transaction (that is not trapped somehow) triggers a ROLLBACK for the whole transaction automatically. You don't have to do anything extra. BEGIN; CREATE TABLE IF NOT EXISTS blah ( ... ); -- much more DDL COMMIT; You cannot start, commit or roll back ...


0

There will be no difference in performance between a view and a straight-up query. View is basically a saved query text. If you expect to call this exact query in multiple places in your code, it may make sense to create a view. Otherwise, put a query in your pgplsql code, it will improve readability of it.



Top 50 recent answers are included