Tag Info

Hot answers tagged

39

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


21

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, which is available for download here: Postgres source directories. Here is an example, assuming item_id is your Primary Key: ...


19

Functions with LANGUAGE sql are basically just batch files with plain SQL commands in a function wrapper (and therefore atomic, always run inside a single transaction) accepting parameters. Currently (including pg 9.5) all statements in an SQL function are planned at once, which is subtly different from executing one statement after the other and may affect ...


18

It can be done, using the following template: CREATE TABLE tablename ( ... ); /* for direct invocation */ CREATE FUNCTION propagate_data(newrow tablename) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO other_table VALUES (newrow.a, newrow.b, ...); END: $$; /* trigger function wrapper */ CREATE FUNCTION propagate_data_trg() RETURNS trigger ...


18

It depends. With SQL functions (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 within and pass parameters. With PL/pgSQL functions (LANGUAGE plpgsql), the answer is normally yes. However, PL/pgSQL allows for dynamic SQL where passed ...


17

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


17

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


13

as superuser: create or replace function bytea_import(p_path text, p_result out bytea) language plpgsql as $$ declare l_oid oid; r record; begin p_result := ''; select lo_import(p_path) into l_oid; for r in ( select data from pg_largeobject where loid = l_oid order by pageno ) loop ...


13

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


12

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


12

Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc. That's a holdover from PostQUEL. Any features that some people (possibly with experience in ...


10

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


9

This solution isn't exactly efficient in terms of runtime, but it's trivially easy compared to making your own headers for COPY BINARY. Further, it doesn't require any libraries or scripting languages outside of bash. First, convert the file into a hexdump, doubling the size of the file. xxd -p gets us pretty close, but it throws in some annoying newlines ...


9

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


8

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.


8

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


8

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


8

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


8

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_demo() RETURNS TRIGGER AS $func$ DECLARE test ...


7

If you are open to a different approach, Have you considered adding a 'deleted' Boolean flag to the table, or a 'deleted_at' timestamp instead. Or better still, deny CRUD access to your database tables and handle the audit trail in your transactional API :)


7

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


7

I think this is because you only ever return the first row from the query's result. The select ... into ... will only retrieve one row and the query select * from result returns only that single record: You also don't need a PL/pgSQL function, a plain SQL function will work just fine: CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS ...


6

In terms of DDL, Postgres does not have procedure objects, only functions. Postgres functions can return value(s) or void so they take on the roles of both functions and procedures in other RDBMSs. The word 'procedure' in the create trigger refers to a function. In terms of the Postgres documentation, 'procedure' is also a synonym for the database object ...


6

plpgsql is a full-fledged procedural language, with variables, looping constructs, etc. A SQL function is simply a subquery. A SQL function, if it is declared STABLE or IMMUTABLE and not also declared STRICT, can often be inlined into the calling query, as if it were written out on each reference.


6

Answer The error occurs here: CASE tmp_code WHEN COALESCE(tmp_code,0)=0 THEN Would have to be CASE WHEN COALESCE(tmp_code,0)=0 THEN You are mixing the two different syntax variants of PL/pgSQL CASE ("simple case" vs. "searched case") in an incompatible way. There is another error: update netcen.test set test=myobj.test, testname=myobj.testname ...


6

You don't need a the function at all, this can be done with a single SQL statement: with recursive tree as (id, parent) ( select link as id, parent from linktable where id = itemid union all select c.link as id, c.parent from linktable c join tree p on p.id = c.parent ) select dt.id, dt.value from tree ...


6

Use GET DIAGNOSTICS integer_var = ROW_COUNT; More in the manual in the chapter Obtaining the Result Status. Your example could look like this: CREATE OR REPLACE FUNCTION f_test() RETURNS SETOF table_a AS $func$ DECLARE i int; ct int := 0; BEGIN RETURN QUERY SELECT * FROM table_a; -- 14 records GET DIAGNOSTICS i = ROW_COUNT; ct := ct + i; ...


6

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though. Requires Postgres 9.0 or later (workaround for older versions possible). CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass) RETURNS void AS $func$ BEGIN FOR _tbl_in IN EXECUTE format('SELECT * FROM %s', pg_typeof(_tbl_in)) LOOP -- do ...


6

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


5

Generally speaking moving application logic into the database will mean it is faster - after all it will be running closer to the data. I believe (but am not 100% sure) that SQL language functions are faster than those using any other languages because they do not require context switching. The downside is that no procedural logic is allowed. PL/pgSQL is ...



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