Hot answers tagged

4

You can use RAISE without any parameters. This is documented on the Errors and Messages page: The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's EXCEPTION clause; it causes the error currently being handled to be re-thrown. CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC) RETURNS ...


4

First of all, SQL functions or procedures (LANGUAGE SQL) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures alike. (Maybe other PLs, too.) Like dezso commented, do not confuse plpgsql BEGIN (starting a block) with SQL BEGIN; (starting a transaction). ...


4

This is (almost) equivalent to your function in the question, but performs like a plain SELECT: CREATE OR REPLACE FUNCTION notifications_get_faster() RETURNS SETOF private.notifications AS $func$ SELECT * FROM private.notifications $func$ LANGUAGE sql STABLE; Almost, because it's not SECURITY DEFINER, which would prevent the desired effect. Most ...


4

Some basic rules: parameters or variables are not prefixed with @ in PL/pgSQL (because @ is invalid in an identifier in SQL) however, it is highly recommended to prefix parameters with some distinct prefix to make sure their names to not conflict with column names. Many people use the prefix p_ but of course you can use something different. every statement ...


4

The equivalent of TRY-CATCH error handling in PostgreSQL is a block of code in this way: [ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END; Have a look at ...


3

I think something like this should do it: SELECT DISTINCT pg_class.oid::regclass FROM pg_rewrite JOIN pg_depend ON pg_depend.classid = 'pg_rewrite'::regclass AND pg_depend.objid = pg_rewrite.oid AND pg_depend.refclassid = 'pg_class'::regclass AND pg_depend.refobjid <> pg_rewrite.ev_class JOIN pg_class ON pg_class.oid = pg_depend.refobjid ...


2

NEW is a record, not a table. Basics: Use NEW in FROM clause in Postgres trigger? Slightly modified setup CREATE TABLE product ( product_id serial PRIMARY KEY, product_name text UNIQUE NOT NULL -- must be UNIQUE ); CREATE TABLE purchase ( purchase_id serial PRIMARY KEY, product_id int REFERENCES product, when_bought date ); CREATE VIEW ...


2

You can not access a environment variable directly; but you can access a custom configuration setting and set that to a value from the environment at startup. For example, you can pass the follow startup option using PGOPTIONS: PGOPTIONS="-c 'custom.some_string=${SOME_STRING}'" Note: the dot (.) in the name is important. Then retrieve the value using ...


2

Generally PLpgSQL doesn't allow dynamic work over fields of composite types. This languages is pretty static - more similar to older compilied languages like Pascal, Ada, Modula. Some modern version of PostgreSQL allows transformation record->json and Postgres has lot of functions for work with JSON values (I use row_to_json and json_each_row` functions): ...


2

The manual for psql: Variable interpolation will not be performed within quoted SQL literals and identifiers. The body of a DO statement (or function) is a quoted literal. Dollar-quoted in this case, but all the same: $$ DECLARE _now_date timestamp := :now_utc; BEGIN -- END $$ Insert text with single quotes in PostgreSQL To enable SQL ...


2

If the file contains a valid JSON literal, you could read it in with pg_read_file() and assign to a json variable directly: CREATE OR REPLACE FUNCTION file_read(file text) RETURNS void AS $func$ DECLARE content json := pg_read_file(file, 0, 10000000); -- arbitrary max. 10 MB BEGIN -- do some more stuff here END $func$ LANGUAGE plpgsql; But that ...


2

You can certainly write triggers, functions and stored procedures (since Postgres 11) in languages other than C - the most popular is probably PL/pgSQL which is similar to Oracle's PL/SQL. You can also write functions in SQL which is more efficient for simply "query encapsulation". I would implement the function in your question as a simple SQL function: ...


1

Your guess is pretty close, you'll need dynamic SQL. But this should be considerably faster and more elegant than looping through all columns in the NEW record etc.: CREATE OR REPLACE FUNCTION trg_jsonb_row_with_fk() RETURNS trigger AS $func$ DECLARE _sql text; _jsonb_row jsonb; BEGIN SELECT 'SELECT to_jsonb($1) || ' || string_agg( ...


1

You can't use double quotes for string literals. Do include a single quote in a string literal, it has to be escaped by doubling them: query := 'unload (sql) to ''s3_loc/user-data.csv'' iam_role ''role'''; Additionally can't use names of variables from PL/pgSQL in a SQL string. You need to include the content of that variable in the query string you build: ...


1

You are using an AFTER trigger - thus any action will have already taken place and the UPDATE will actually run twice: once the for the "original" statement and then again from within your trigger. It seems to be, you actually want a BEFORE trigger. And there you just assign values to the NEW row, rather than UPDATEing the table itself. So I think what ...


1

The most efficient solution is to not fire the trigger in that case: CREATE TRIGGER trigger_tabel AFTER INSERT OR UPDATE OF (column_a) --<< only fire the trigger if column_a is updated OR DELETE ON name_table FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview(); If you have multiple columns that should trigger the refresh, you can separate ...


1

Your code is executed in a single DO statement and hence in a single transaction. So if any of the statements cause an error, all statements will be rolled back. If you want to avoid that, you can use subtransactions by using an EXCEPTION clause, for example: BEGIN EXECUTE format('DROP SEQUENCE %I.%I', myschema.schema_name), ...


1

First: user is a reserved keyword, you need to use double quotes if you want to use it as a table name: "user" But I strongly suggest you find a different name. To answer your immediate question: just remove the final select from the CTE - that's the one cause the error (as you need to store the result of that select somewhere). CREATE OR REPLACE FUNCTION ...


1

For just the shown example, plain string manipulation would do the job: WITH tbl(j) AS (SELECT json '[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]') SELECT '{' || regexp_replace( left(right(j::text, -1), -1) , '"([^"]+)" *:', '\1=', 'g') || '}' FROM tbl; If you have more sophisticated keys and values, here is a rewrite of ...


1

It depends on the client-side verbosity. When using psql, issuing \set VERBOSITY verbose before calling the statement would caused the error message to include: TABLE NAME: accounts COLUMN NAME: username In recent versions of psql, it's also possible to issue \errverbose after the fact to get the last error message reformated in verbose form.


1

Use jsonb_each(jsonb) or jsonb_each_text(jsonb) in a FOR loop like: CREATE OR REPLACE FUNCTION my_function(input jsonb) RETURNS jsonb LANGUAGE plpgsql AS -- language declaration required $func$ DECLARE _key text; _value text; BEGIN FOR _key, _value IN SELECT * FROM jsonb_each_text($1) LOOP -- do some math operation on its ...


1

The lo_import is successful, creating a 4GB large object. But the hard limit for the bytea datatype is 1GB, so these contents are not transferable into a bytea.


1

To pass an actual array for a VARIADIC parameter, you must use the keyword VARIADIC in the call: SELECT mix_table_fields('art'::VARCHAR , 'out'::VARCHAR , VARIADIC array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]); Even works without explicit type casts in your case. Function type ...


1

The solution I was looking for is this: CREATE OR REPLACE FUNCTION test_insert(daco TEXT, _journal_arr cashreg_journal[]) RETURNS TEXT LANGUAGE plpgsql AS $function$ DECLARE jr cashreg_journal; BEGIN FOREACH jr IN ARRAY _journal_arr LOOP INSERT INTO cashreg_journal(gid, device_id, item, vat_percentage, vat_sum, price) SELECT jr.gid, jr....


1

Following the link you have supplied and carefully reading the good answers of a_horse_with_no_name and Erwin Brandstetter I've tried to solve this question. Use array of composite type as function parameter and access it First I've created a composite TYPE: CREATE TYPE cr_journal AS ( gid INTEGER, device_id TEXT, item TEXT, vat_percentage INTEGER,...


1

Burns down to a simple solution: _y := to_jsonb(ARRAY[_x]) || _y; This is concatenating two jsonb arrays with the || operator.. You need to wrap the simple text value _x into a jsonb array for this. There are various way, to_jsonb(ARRAY[_x]) is one. Or even just: _y := to_jsonb(_x) || _y; The manual: The || operator concatenates the elements at the ...


1

Yes, for example: FUNCTION insert_file (IN filepath VARCHAR(255)) ... sqltxt:='COPY table_name from '''||filepath||''' with (FORMAT csv)'; execute sqltxt; '''||variable||''' will produce 'value' with quotation marks. '||variable||' will produce the value without quotation marks. usefully for table names and integers for example. You need to declare ...


1

What you might need You should be able to avoid all the complication with a simple SELECT instead: SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2 FROM tbl t -- replace tbl with actual table JOIN ST_GeomFromText($clipper_geom, 4326) AS x(geom) ON ST_Intersects(x.geom, t.geom) WHERE t.seq = $age_sequence; Minor difference: you get the original ...


1

hstore You can use hstore for this with it's akeys to return the keys as an array. CREATE EXTENSION hstore; SELECT akeys(hstore(t)) FROM ( VALUES (1,2,3) ) AS t(a,b,c); akeys --------- {a,b,c} Or use skeys to return a set SELECT skeys FROM ( VALUES (1,2,3) ) AS t(a,b,c) CROSS JOIN LATERAL skeys(hstore(t)); skeys ------- a b c


1

The only solution to commit within "DO" blocks (or functions) (for Postgresql version less then 11) is to use dblink connection to the same server an execute your queries there. Just keep in mind variables and temporary objects visibility. more information about dblink Starting with Postgresql-11 transaction control from inside of "DO" block is available ...


Only top voted, non community-wiki answers of a minimum length are eligible