PostgreSQL: trigger to call function with parameters

Tag: postgresql

In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments:

ERROR:  function create_rec_if_needed() does not exist: 
      CREATE TRIGGER after_update_winks AFTER UPDATE ON winks
      FOR EACH ROW
      WHEN (NEW.counter > 3)
      EXECUTE PROCEDURE create_rec_if_needed(NEW.id, 'Wink')

When I list extant functions, I can see my 2-argument function. Here's the output of \df:

                                                          List of functions
 Schema |          Name           |     Result data type      |                          Argument data types    
--------+-------------------------+---------------------------+-------------------------------------------------
 public | create_rec_if_needed    | void                      | rec_id integer, rec_type text

Can anyone shed a bit of light on why my trigger isn't making use of the existing function? Is there an alternative design I should use to reach my goal? My aim is for multiple tables to have a trigger that calls the function create_rec_if_needed, and in each case, the trigger must supply the id of the record and a table-specific string.

Best How To :

A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger.

You can use the arguments passed to the trigger function via TG_ARGV, e.g. TG_TABLE_NAME - the name of the table that caused the trigger invocation. 40.9. Trigger Procedures.

In PostgreSQL is it possible to join between table and function?

sql,postgresql

Your table uses a carid value to retrieve the corresponding part_ids from function PartsPerCar() which returns a set of rows, a so-called table function. In sub-section 5 (keep on reading) we see that Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions...

Return integer value of age(date) function in Postgres

postgresql

Here is how you get the number of days comparing two dates: SQL> select extract(day from now()-'2015-02-21'::timestamptz); date_part ----------- 122 (1 row) ...

How to get second row in PostgreSQL?

sql,postgresql

This query uses WITH construction that works similar to sub-queries. Investigate this query with EXPLAIN before use in production because it may be slow on big tables: WITH orders AS ( SELECT email , first_value(dt_cr) OVER wnd1 AS min_date , nth_value(dt_cr, 2) OVER wnd1 AS second_date FROM orders WINDOW wnd1...

Redirect if ActiveRecord::RecordNotUnique error exists

ruby-on-rails,postgresql,activerecord,error-handling

Just the way you catch every other error begin Transaction.create!(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now) rescue ActiveRecord::RecordNotUnique redirect_to root_path end ...

JSONB: more than one row returned by a subquery used as an expression

sql,postgresql,postgresql-9.4,jsonb,set-returning-functions

The error means just what it says: more than one row returned by a subquery used as an expression The expression in the WHERE clause expects a single value (just like you substituted in your added test), but your subquery returns multiple rows. jsonb_array_elements() is a set-returning function. Assuming this...

PostgreSQL can't find column

java,postgresql

Just in case the issue it related to upper and lower case in the column name: it's possible to put the column name in double quotes: PreparedStatement ps = conn.prepareStatement("SELECT * FROM produits where \"NOM_PRODUIT\" like ?"); This way the name is case sensitive....

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions

Query The query is not as simple as it looks at first. The shortest query string does not necessarily yield best performance. This should be as fast as it gets, being as short as possible for that: SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT...

Postgres Index-only-scan: can we ignore the visibility map or avoid heap fetches?

postgresql,indexing

There is no way to do what you want in PostgreSQL as it stands. It'd be interesting to do but a fair bit of work, very unlikely to be accepted into core, extremely hard to do with an extension, and likely to have worse side-effects than you probably expect. You'd...

How to customize the configuration file of the official PostgreSQL docker image?

postgresql,docker

The postgres:9.4 image you've inherited from declares a volume at /var/lib/postgresql/data. This essentially means you can can't copy any files to that path in your image; the changes will be discarded. You have a few choices: You could just add your own configuration file as a volume at run-time with...

Is there a better solution to join the same dataset to get the distinct count of a dimension used for aggregation?

sql,postgresql

You can simply do a Group Count on the result of the aggregation: SELECT dim1, dim2, COUNT(*) OVER (PARTITION BY dim1), SUM(measure1) measure1, SUM(measure2) measure2 FROM test GROUP BY dim1, dim2 ...

Spring Boot - How to set the default schema for PostgreSQL?

java,spring,hibernate,postgresql

You can try setting the default schema for the jdbc user. 1) ALTER USER user_name SET search_path to 'schema' 2) Did you try this property? spring.datasource.schema http://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html...

How to group following rows by not unique value

sql,postgresql,greatest-n-per-group,window-functions,gaps-and-islands

If your case is as simple as the example values suggest, @Giorgos' answer serves nicely. However, that's typically not the case. If the id column is a serial, you cannot rely on the assumption that a row with an earlier time also has a smaller id. Also, time values (or...

How to order SQL query result on condition?

sql,postgresql,order,condition

Use CASE expression in ORDER BY clause: SELECT category FROM ( SELECT DISTINCT category FROM merchant ) t ORDER BY CASE WHEN category = 'General' THEN 0 ELSE 1 END, category ASC CASE guarantees that rows with General will be sorted first. The second argument orders the rest of the...

Update enum column in Laravel migration using PostgreSQL

postgresql,laravel,laravel-5,laravel-migrations

Laravel use constraint on character varying for enum. Assuming there is a table mytable with an enum column status, we have to drop the constraint (named tablename_columnname_check) then add it in a migration like this: DB::transaction(function () { DB::statement('ALTER TABLE mytable DROP CONSTRAINT mytable_status_check;'); DB::statement('ALTER TABLE mytable ADD CONSTRAINT mytable_status_check...

Postgresql Update JDBC

java,postgresql,jdbc

You shouldn't build SQL by putting your variables directly via string concatenation. What happens here is that with 11, your SQL becomes: set last=11 Which is valid SQL (using 11 as a integer literal), while with xx it becomes: set last=xx There are no quotes, so the SQL means you're...

Subtract two columns of different tables

sql,postgresql,sum,aggregate-functions,subtract

I interpret your remark but that result can't to be negative as requirement to return 0 instead of negative results. The simple solution is GREATEST(): SELECT GREATEST(sum(amount) - (SELECT sum(amount) FROM solicitude WHERE status_id = 1 AND user_id = 1), 0) AS total FROM contribution WHERE user_id = 1; Otherwise,...

Formatting dates in PostgreSQL

sql,postgresql,select,date-formatting

You can use the to_char function to format your column: SELECT TO_CHAR(ddatte, 'dd/Mon/yyyy') FROM mytable ...

Sqoop Export with Missing Data

sql,postgresql,shell,hadoop,sqoop

I solved the problem by changing my reduce function so that if there were not the correct amount of fields to output a certain value and then I was able to use the --input-null-non-string with that value and it worked.

Save a hex-string to PostgreSQL column character varying

postgresql,hex

If you are sure that there are never more than 10 elements, you can simply cast your hex string to text: INSERT INTO my_table (hex_text) VALUES (<some hex data>::text); Or use a bytea column instead?...

JPA NamedNativeQuery syntax error with Hibernate, PostgreSQL 9

java,hibernate,postgresql,jpa

So the problem was that em.createNativeQuery(...) was not the correct invocation of a NamedNativeQuery in order to do that I should've invoked em.createNamedQuery(...). However, seeing that em.createNativeQuery(...) does not accept @SqlResultSetMapping it is very difficult to map the result to a custom class. The end solution was to use return...

name of value returned from PostgreSQL function

postgresql,plpgsql

You can choose between: select aschema.afunction() as my_name; -- like in IMSoP's answer select my_name from aschema.afunction() as my_name; -- with alias select afunction from aschema.afunction(); -- with function name If you add aschema to search path, you can omit schema identifier: set search_path to public, aschema; select afunction() as...

How to insert and Update simultaneously to PostgreSQL with sqoop command

postgresql,hadoop,hive,sqoop

According to my internet search, it is not possible to perform both insert and update directly to postgreSQL DB. Instead you can create a storedProc/function in postgreSQL and you can send data there.. sqoop export --connect <url> --call <upsert proc> --export-dir /results/bar_data Stored proc/function should perform both Update and Insert....

Postgres SQL constraint a character type

postgresql,constraints

Use a check constraint: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL, constraint check_allowed check (my_text in ('A', 'B', 'C')) ); More details in the manual: http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS...

How to create a SELECT query FROM “TABLE1 AND TABLE2”

sql,postgresql,select,join

UNION ALL SELECT field1, field2, field3 FROM table1 WHERE condition UNION ALL SELECT field1, field2, field3 FROM table2 WHERE condition; Or to simplify your WHERE condition SELECT * FROM ( SELECT field1, field2, field3 FROM table1 UNION ALL SELECT field1, field2, field3 FROM table2 ) WHERE condition; ...

What happens with duplicates when inserting multiple rows?

sql,postgresql,exception,duplicates,upsert

The INSERT will just insert all rows and nothing special will happen, unless you have some kind of constraint disallowing duplicate / overlapping values (PRIMARY KEY, UNIQUE, CHECK or EXCLUDE constraint) - which you did not mention in your question. But that's what you are probably worried about. Assuming a...

postgresql complex group by in query

sql,postgresql

SELECT itemid, deadlineneeded, sum(quantity) AS total_quantity FROM <your table> WHERE (deadlineneeded - delievrydate)::int >= 1 GROUP BY 1, 2 ORDER BY 1, 2; This uses a "delievrydate" (looks like a typo to me) that is at least 1 day before the "deadlineneeded" date, as your sample data is suggesting....

How to delete replication slot in postgres 9.4

postgresql,replication,postgresql-9.4

Use pg_drop_replication_slot: select pg_drop_replication_slot('bottledwater'); See the docs and this blog. The replication slot must be inactive, i.e. no active connections. So if there's a streaming replica using the slot you must stop the streaming replica. Or you can change its recovery.conf so it doesn't use a slot anymore and restart...

How to check what constraint has been violated?

java,sql,postgresql,exception

something like below catch (ConstraintViolationException conEx) { if (conEx.getConstraintName().contains("xyz_fK")) { //TODO Project Entity is violating it's constrain } LOGGER.Info( "My log message", conEx.getConstraintName()); LOGGER.ERROR( "My log message", conEx); ...

Avoid calling COUNT twice in CASE expression (PostgreSQL)

sql,postgresql

This expression: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' is not slow because COUNT() is called twice. The hard work of aggregating the data is the part where the key values are brought together. The individual aggregation functions are generally not particularly expensive (there are exceptions such as...

plv8 disadvantages or limitations?

postgresql,plpgsql,plv8

The advantages and disadvantages of PLV8 are same as advantages and disadvantages of PLPerl, PLPython and other PL languages. It is not integrated with PostgreSQL engine - the processing SQL statements result can be slower. PLpgSQL is fully integrated to PostgreSQL engine. SQL is not integrated to language - isn't...

PostgreSQL order by TSRange

postgresql

There are range functions described in documentation. SELECT * FROM my_table ORDER BY lower(range_column); ...

Subtract hours from the now() function

sql,postgresql,datetime,timezone,date-arithmetic

Answer for timestamp You need to understand the nature of the data types timestamp without time zone and timestamp with time zone (names can be deceiving). If you don't, read this first: Ignoring timezones altogether in Rails and PostgreSQL The AT TIME ZONE construct transforms your timestamp to timestamptz, which...

Retrieve updated rows in AFTER UPDATE trigger Postgresql

postgresql,triggers,plpgsql

You can create a temporary table (so that it will visible only in the session). In the row level trigger you insert the rows into the temporary table, in the statement level trigger you select (and delete) from the temporary table.

PostgreSQL: trigger to call function with parameters

postgresql

A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. You can use the arguments passed to the trigger function via TG_ARGV, e.g. TG_TABLE_NAME - the name of the table that caused the trigger invocation....

postgresql mathematical formula error

postgresql

You are trying to use COUNT(sale_order_line.name) as a group by item. Aggreagte functions work on grouped item. They are not for grouping them. I do not know your tables but try Select stock_inventory_line.product_code AS Sku, COUNT(sale_order_line.name) AS Qty_Sold, stock_inventory_line.product_qty AS Current_Qty, (stock_inventory_line.product_qty / COUNT(sale_order_line.name)) AS NOM From sale_order_line, product_product, product_template,...

Column does not Exist in postgresql?

sql,postgresql,postgresql-9.1

You can DRY up the duplication of the projection with a CTE, and then use this in your WHERE predicate: WITH myCte AS ( select order_id , order_item_id , sku ,merchant_payable, order_created_at , case when name like 'Rise%' then amount-(((amount*12.14)/100)+ ((amount*3.08)/100) + 51.30) when name like 'Masha%' then amount-(((amount*9.10)/100)+ ((amount*3.08)/100)...

How to install / use orafce package in postgresql 9.4?

postgresql,debian,orafce

Ok, a smple CREATE EXTENSION orafce is enough...

Error while trying to insert data using plpgsql

postgresql,timestamp,plpgsql

CURRENT_TIME is a reserved word (and a special function), you cannot use it as variable name. You don't need a variable here to begin with: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) AS $func$ BEGIN INSERT INTO "TEST"(created) VALUES (now()) RETURNING id INTO pid; END $func$ LANGUAGE plpgsql; now() is...

what is the SQL prepared stament for lo_import in postgreSQL

c++,sql,database,postgresql,odbc

The query to prepare should be insert into test values(?,lo_import(?)); You proposal insert into test values(?,?) can't work because you can't submit a SQL function call (lo_import) as the value for a placeholder (?). Placeholders only fit where a literal value would fit. When you're asking what's the prepared statement...

PostgreSQL conditional statement

sql,postgresql,if-statement

Try this: with c as (select count(*) cnt from table1) select table2.* from table2, c where c.cnt < 1 union all select table3.* from table3, c where c.cnt >= 1 ...

load the data from file using multi threading

java,postgresql

Writing something to a storage will prevent all other threads from writing to the same. You cannot simply make everything multithreaded. In this case all other parts Need to wait for the previous one to finish....

group by is not working in postgreSQL

sql,postgresql,group-by

ID is unique and group by ID works just like a plain select. Column createdAt is not unique and results with same createdAt value must be grouped. You should provide a way how they will be grouped - use aggreagete function, remove them from select clause or add them to...

How to use Rails #update_attribute with array field?

ruby-on-rails,ruby,postgresql,ruby-on-rails-4,activerecord

I don't think update_attribute is going to be useful as it will replace the array with the new value rather than append to it (but see better explanation below in --Update-- section). I'm not sure what best practices are here, but this should work to just add something if it...

Connecting C++ and Postgresql

c++,postgresql,linker-error,libpqxx

You need a makefile and you need to include your linker flag for pqxx. On my linux box the linker flag is -lpqxx. See my example makefile below. CXXFLAGS := LDFLAGS := -lpqxx # Executable output command $(EXE): $(OBJECTS) $(CXX) $(CXXFLAGS) $(LDFLAGS) -o [email protected] $^ # build rule for c++...

postgres: using previous row value when current row value is null

postgresql

The below query fills empty values in the resultset of your original query. The method consists in splitting the data into partitions according to the number of empty values and selecting the first (non-empty) value from each partition (add * to the select to see how it works). WITH survey...

Syntax error while creating table in PostgreSQL 8.1

postgresql

PostgreSQL 8.1 only supports INCLUDING DEFAULTS. You'll either have to upgrade to at least 8.3 or create the indices manually....

need help specifying potentially reserved words as strings in postgres query

postgresql

For string literals, you should you single quote instead of double quote: UPDATE rv_template_fields SET view = 'display_type_1' WHERE rv_template_fields.view = 'display_type_2' Double quotes are for quoting identifiers of fields and relations, like, for instance view, so that you could write also: UPDATE rv_template_fields SET "view" = 'display_type_1' WHERE "view"...

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders

postgresql

Prepared statements are used to speed up the repeated execution of the same query with different arguments. If your aim is to insert many rows at once it is better to execute regular insert query, which will be faster than the prepared insert. However, if you insisted on this solution,...

Speed up Min/Max operation on postgres with index for IN operator query

postgresql,postgresql-9.3

It turned out (please see comments), that this query: SELECT MIN(minimal) AS minimal FROM ( SELECT MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?,?,?,?,?,?,?) GROUP BY "tag_id" ) some_alias is able to deceive PostgreSQL in such a way, that it performs better because, as I guess, it uses the...

Translation of interval

postgresql,datetime,translation,intervals,postgresql-8.4

I think no. sorry. day, month etc are field in type interval. it will be in English. like you don't expect use "vybrac" instead of select :) But you can have locale in time values, yes. td=# set lc_time TO pl_PL; SET td=# SELECT to_char(to_timestamp (4::text, 'MM'), 'TMmon'); to_char ---------...