Hot answers tagged

105

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 ...


50

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, ...


39

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 ...


29

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 ...


21

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


19

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 ...


19

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 ...


19

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....


18

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, ...


16

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: ...


16

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

Actually, you do not have to expand the record manually. As long as number, sequence and types of columns match between the two tables, you can use this much simpler form: EXECUTE 'INSERT INTO my_table SELECT ($1).*' USING NEW; Since your table name seems to be stable, you do not even need dynamic SQL with EXECUTE. Just a plain INSERT: INSERT INTO ...


14

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 ...


14

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in: CREATE FUNCTION test() RETURNS SETOF RECORD AS $$ DECLARE rec record; BEGIN select 1,2 into rec; return next rec; select 3,4 into rec; return next rec; END $$ language plpgsql; Caller: => select * from test() as x(a ...


13

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 ...


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

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 ...


13

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also, misleadingly, 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 record holding the new row: NEW Data type ...


13

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 as ...


13

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 ... ...


12

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; ...


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

(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 ...


12

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here. CREATE OR REPLACE FUNCTION insert_records_for_notification( _username text , _state text , _district text , _bloodgroup text , _status text , _approverejectstatus text , _emailsubject text , ...


11

To pick a random row, see: quick random row selection in Postgres SELECT * FROM words WHERE Difficult = 'Easy' AND Category_id = 3 ORDER BY random() LIMIT 1; Since 9.5 there's also the TABLESAMPLE option; see documentation for SELECT for details on TABLESAMPLE.


11

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. ...


11

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

A trigger can only ever call one tigger function, so no to item 1. The preferable form is item 2. IMO. You can put as many SQL statements into a single plpgsql function as you want. Item 3. is possible, too. Well, not exactly the same trigger, the name would have to be different. Triggers on the same event fire in alphabetical order, btw. But I see no gain ...


10

The table itself is only locked in ROW EXCLUSIVE mode, which shouldn't prevent any normal operations on the table, only things like DROP, ALTER, and CREATE INDEX. Each individual row that is being deleted will be locked for the duration. This should only block other processes if those other processes are trying to update the rows (or delete them themselves)...


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