Hot answers tagged

97

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. Two tables in the same schema cannot have an index of the same name. (Follows logically.) If you do not care about the name of ...


46

Strictly speaking, the term "stored procedures" points to SQL procedures in Postgres, introduced with Postgres 11. Related: When to use stored procedure / UDF? There are also functions, doing almost but not quite the same, and those have been there from the beginning. Functions with LANGUAGE sql are basically just batch files with plain SQL commands in a ...


41

As said in "40.5.3. Executing a Query with a Single-row Result" (emphasis mine): The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. So this should work: SELECT col1, ...


35

It depends. SQL functions With LANGUAGE sql, the answer is generally yes. Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from the body and pass parameters. PL/pgSQL functions With LANGUAGE plpgsql, the answer is normally yes. However, PL/pgSQL allows for dynamic SQL where passed ...


27

PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well: Use views where possible Where a view is not suitable, use an SQL function Where an ...


27

UPDATE (2015-08-20): There is now an official implementation for handling upserts through the use of ON CONFLICT DO UPDATE (official documentation). At the time of this writing, this feature currently resides in PostgreSQL 9.5 Alpha 2, which is available for download here: Postgres source directories. Here is an example, assuming item_id is your Primary ...


26

as superuser: create or replace function bytea_import(p_path text, p_result out bytea) language plpgsql as $$ declare l_oid oid; begin select lo_import(p_path) into l_oid; select lo_get(l_oid) INTO p_result; perform lo_unlink(l_oid); end;$$; lo_get was introduced in 9.4 so for older versions you would need: create or replace ...


21

PostgreSQL now has UPSERT. The preferred method according a similar StackOverflow question is currently the following: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN ...


18

Exception blocks are meant for trapping errors, not checking conditions. In other words, if some condition can be handled at compile time, it should not be trapped as error but resolved by ordinary program logic. In Trapping Errors section of PL/PgSQL documentation you can find such tip: Tip: A block containing an EXCEPTION clause is significantly more ...


17

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION pg_temp....


16

Use pg_read_file('location_of file')::bytea. For example, create table test(id int, image bytea); insert into test values (1, pg_read_file('/home/xyz')::bytea); Manual


15

You just need to move the old data into the restrictions_deleted table before it gets deleted. This is done with the OLD data type. You can use a regulat INSERT statement and and use the OLD values as the values-to-be-inserted. CREATE TRIGGER moveDeleted BEFORE DELETE ON restrictions FOR EACH ROW EXECUTE PROCEDURE moveDeleted(); CREATE FUNCTION ...


15

You should be able to use auto-explain. Turn it on and SET auto_explain.log_min_duration = 0; and you should get the plans in your log for all statements run in that session. You might also want to set SET auto_explain.log_analyze = true; but you'll essentially run everything double - once for 'real' and once to EXPLAIN ANALYZE on. During a non-...


15

Answer is yes. :) CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30)) RETURNS VOID AS $func$ BEGIN EXECUTE format(' CREATE TABLE IF NOT EXISTS %I ( id serial PRIMARY KEY, customerid int, daterecorded date, value double precision )', 't_' || t_name); END $func$ LANGUAGE plpgsql; I am using format() with %I to sanitize ...


14

I addition to @rfusca's advice: SQL statements inside plpgsql functions are considered nested statements and you need to set the additional Parameter auto_explain.log_nested_statements. Unlike some other extensions, you don't have to run CREATE EXTENSION for this one. Just load it dynamically into your session with LOAD. Your session could look like this: ...


13

I could not find a direct way to output the the CONTEXT line with a user-defined exception. This option is just not implemented (yet) in PostgreSQL 9.1. Read the manual here. However, I found a ... Workaround ... that should perform flawlessly. You can make plpgsql behave as desired by calling another function that raises the error for you. This works with ...


13

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function. Demo CREATE OR REPLACE FUNCTION trg_demo1() RETURNS TRIGGER AS $func$ DECLARE ...


13

Using OUT parameters achieve basically the same thing as in @klin's answer, but without creating user-defined types. Just move all your variables from the declare block into the argument-list as OUT parameters: create or replace function get_user_info( IN _id varchar, OUT is_banned boolean, OUT reputation integer, OUT is_vip boolean, ...


13

Quick and dirty In Postgres 9.4+ use SELECT to_regclass('foo'); Returns NULL if the identifier is not found in the search path. In Postgres 9.3 or older use a cast to regclass: SELECT 'foo'::regclass; This raises an exception, if the object is not found! If 'foo' is found, the oid is returned in its text representation. That's just the table name, ...


12

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947 Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html


12

UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same. Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge ...


12

First, the correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL: EXPLAIN ANALYZE SELECT f1(); Optimization PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be ...


11

You can examine a special variable FOUND of a type boolean. From the documentation: FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements: A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned. A PERFORM statement sets FOUND true if it produces (...


10

In PostgreSQL, every table name serves as type name for the row type (a.k.a. composite type) automatically - not a table type, there are no "table types" or "table variables" in Postgres (but there are typed tables). So you can just declare a variable of that type in PL/pgSQL. CREATE FUNCTION foo() RETURNS void LANGUAGE plpgsql AS $func$ DECLARE q1 foo; ...


10

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also called procedure) with a function body and 0-n triggers (without body) calling this function. The special variable NEW in plpgsql trigger functions is neither a map nor an array; it's a row: NEW Data type RECORD; variable holding the new database ...


10

(Obvious error in the trigger logic aside.) In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops). This ...


10

Upsert on partitioned tables is not implemented in versions earlier than Postgres 11. In Postgres 9.6: INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations. Declarative partitioning does not resolve the ...


10

As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE conflicts with ... ...


9

Some DO's: Use SQL as the function language when possible, as PG can inline the statements Use IMMUTABLE / STABLE / VOLATILE correctly, as PG can cache results if it's immutable or stable Use STRICT correctly, as PG can just return null if any input is null instead of running the function Consider PL/V8 when you can't use SQL as the function language. It is ...


9

Trigger functions can check the value of the TG_OP variable to determine the event which caused the trigger to fire. There are a number of other special variables as well listed in the documentation.


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