How can I send some http request from postgresql function or trigger
I need to send data via http protocol (GET or POST request) from the function or trigger. Is it possible? ...
plpgsql
creating multi dimensional varchar/text arrays in plpgsql (postgres)
i have some functionality i'd like to get from the server-side code into the database. i can't figure out how to set the values of a multi-demensional varchar array in plpgsql. here is an example of what i'm trying to do: `CREATE OR REPLACE FUNCTION my_function (my_arg integer) RETURNS text[][] AS $$ DECLARE my_arr varchar[]...
PostgreSQL SQL or PL/pgSQL query for traversing a directed graph and returning all edges found
Hi, I'm not particularly accustomed to generating complex SQL queries and am having difficulty in mixing my understanding of procedural languages and of set-based operations in devising a recursive query for network traversal. I wish to find the set of edges that lie 'upstream' of a particular node through conducting a depth first sear...
PSQL row update using NEW statment
Hello, since it is possible to do: INSERT INTO some_table VALUES (NEW.*) in pl/pgsql can we do something similar but for UPDATE clause using NEW ? I mean i want to update some row using values from NEW object. Thanks in advance. ...
ERROR: unterminated quoted string at or near
While executing below shown trigger code using ANT I am getting the error org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer" Position: 57 I am able to sucessfully execute the below code through PGADmin (Provided by postgres) and command line utility "psql" and the trigger functi...
Performance of a SETOF returning function which returns results of another SETOF function?
I have written a function that takes two arguments and returns a SETOF result. CREATE FUNCTION foo(parentId bigint, childId bigint) RETURNS SETOF bar AS ... I would like to write two "wrappers" for this function that make it simpler to call: CREATE FUNCTION foo_parent(parentId bigint) RETURNS SETOF bar AS ... BEGIN RETURN ...
Postgres, plpgsql: Is there a way to connect to other DB from inside of a stored procedure?
I have two DB's one is feed by filtered data from another, now i'm using perl script witch executes query on foreign DB, stores a result in a csv file, and loads it to local DB using \COPY sytnatx Is there a way to write plpgsql function witch will connect to foreign DB and load filtered data in local DB ( I know it can be done in ie. p...
how to convert int to time in a pl/pgsql function
I would like to convert a given date and integer to a timestamp in a pl/pgsql function. i've never done anything with pl/pgsql before, so i'm somewhat at a loss. Thanks to the answer by Pablo Santa Cruz, it got this far: CREATE OR REPLACE FUNCTION to_my_timestamp(mydate date, timeint integer) RETURNS timestamp AS $$ DECLARE myhours...
Shredding XML in postgresql
In SQL Server 2005's T-SQL language I can shred XML value the following way: SELECT t.c.value('./ID[1]', 'INT'), t.c.value('./Name[1]', 'VARCHAR(50)') FROM @Xml.nodes('/Customer') AS t(c) where @Xml is a xml value something like '<Customer><ID>23</ID><Name>Google</Name></Customer>' Can someone help me to achieve the same ...
Query error while working with PL/pgSQL arrays
I have this function: create or replace function insert_aereo( aereo_type[] ) returns text as $$ begin return 'ok'; end $$ language plpgsql; and this is the parameter type that I created: create type aereo_type as (codice int, modello varchar, marca varchar); Then I call my function: select insert_aereo('{123, "ciao", "pippo"}'...
Is it possible to use variables to access columns of records in postgresql trigger
I have a trigger to update my timestamps for each table. I use the following function: CREATE OR REPLACE FUNCTION update_timstamp_table0() RETURNS TRIGGER AS $$ BEGIN IF NEW IS DISTINCT FROM OLD THEN NEW.table0_timestamp_column = extract( 'epoch' from NOW() ) RETURN NEW; ELSE RETURN NULL; END IF; END; $$ LANGUAGE 'plpgsql'; Sinc...
Postgresql optimalization question
Hi! I am wondering if there is any difference between adding LIMIT and OFFSET in plpgsql function body and function call. CREATE FUNCTION test () RETURNS record AS $body$ DECLARE BEGIN select * from producent order by id limit 5 offset 10; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE FUNCTI...
What is the equivalent of PL/SQL %NOTFOUND in PL/pgSQL ?
Everything's in the title. I am Looping on a cursor and would like to have the EXIT WHEN curs%NOTFOUND when there is no more row, what is the equivalent of %NOTFOUND under PostgreSQL ? Edit Or the other cursors attributes %ISOPEN, %EMPTY, etc... ...
How to pass a record as parameter for PL/pgSQL function ?
I keep looking for this answer online but I cannot find it. I am trying to pass one record over a PL/pgSQL function. I tried it in two ways. Fist way : CREATE OR REPLACE FUNCTION translateToReadableDate(mRecord dim_date%ROWTYPE) RETURNS void AS $$ That is the ouput : psql:requestExample.sql:21: ERROR: syntax error at or near "%" L...
Rails misbehaving wrt Postgres SERIAL NOT NULL column
I am developing a (currently) Rails 2.3.x application with a PostgreSQL 8.4 database backend. In my Rails application, I have a model corresponding to a database table that has two columns of datatype SERIAL and set as NOT NULL. I have one of these columns set as the primary key in both Rails and as a PostgreSQL constraint. Table defin...
How to use declared parameters as component of a query in Pl/pgSQL ?
Guys, I am tryingto do a generic function in pl/pgsql. I am facing a problem that I can't figure out. See in the declare block I assigned value to some parameters. I would like to know how to use them as component of a query. (example : the BEGIN/END block, groupby_join_field) CREATE OR REPLACE FUNCTION drillThrough(whereclause any...
pl/pgsql array as input to an aggregate function
hello. i am writing pl/pgsql function that does some statistics processing. using 8.2 of postgres. i want to use this handy aggregate function: regr_slope(Y, X) but, i have my X and Y data stored as local arrays in the pl/pgsql function: y double precision[]; x double precision[]; trouble is when i use this as a line in the pl/pgs...