I have a function written in pl/pgsql which works in the same way as the one described below: CREATE FUNCTION reffunc(text) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test WHERE c1=$1; RETURN $1; END; ' LANGUAGE plpgsql; I want to be able to use this with a single select command as opposed to the documented way w...
I want to create a postgres function that builds the set of columns it returns on-the-fly; in short, it should take in a list of keys, build one column per-key, and return a record consisting of whatever that set of columns was. Briefly, here's the code: CREATE OR REPLACE FUNCTION reports.get_activities_for_report() RETURNS int[] AS $F...
If I have a function in PL/pgSQL that takes in a timestamp, what is the best way to identify whether that date is less than 12 months in the past? e.g. CREATE FUNCTION do_something(foo timestamp) .... -- IF foo is less than 12 months in the past THEN -- do something -- END IF; END; ...
I'm trying to get my first ever trigger and function to work, but how I throw exceptions and return data right way? PostgreSQL 8.4.1 CREATE TABLE "SHIFTS" ( id integer NOT NULL, -- SERIAL added timestamp without time zone DEFAULT now() NOT NULL, starts timestamp without time zone NOT NULL, ends timestamp without time zo...
I am developing an algorithm with Postgres (PL/PgSQL), and I need to calculate the number of working hours between 2 dates, taking into account that weekends are not working and the rest of the days are counted only from 8am to 15pm. Examples: From Dec 3rd at 14pm to Dec 4th at 9am should count 2 hours. (3rd = 1, 4th = 1) From Dec 3rd...
I am new to PostgreSQL and am using the query tool in PGAdmin. I'm trying to run pgsql queries that use variables, but I can't seem to get the syntax right. Here's a sample query that gives a syntax error: DECLARE num INTEGER; BEGIN num := 3; PRINT num; END; ...
I need to make a function that would be triggered after every UPDATE and INSERT operation and would check the key fields of the table that the operation is performed on vs some conditions. The function (and the trigger) needs to be an universal one, it shouldn't have the table name / fields names hardcoded. I got stuck on the part whe...
I have a number of tables that use the Postgres "Partitioning" feature. I want to define a common BEFORE INSERT OF ROW trigger on each table that will 1) dynamically create the partition should the insert occur against the parent table and 2) re-execute the insert against the partition. Something like: CREATE OR REPLACE FUNCTION parti...
CREATE OR REPLACE FUNCTION _chkLogin(userid varchar, pwd varchar) RETURNS BOOLEAN AS $BODY$ DECLARE passed BOOLEAN; BEGIN SELECT (_password = $2) FROM _vRegistration WHERE _userid = $1; RETURN passed; END; $BODY$ LANGUAGE 'plpgsql'; When am executing the code above am getting the following error, SELECT _chkLogin('username','abc...
I'm trying to write a function in PL/PgSQL that have to work with a table it receives as a parameter. I use EXECUTE..INTO..USING statements within the function definition to build dynamic queries (it's the only way I know to do this) but ... I encountered a problem with RECORD data types. Let's consider the follow (extremely simplifie...
Hi I just tried to create my first plpgsql function. When executing the script, I get ERROR: language "‘plpgsql’" does not exist I then run the command CREATE LANGUAGE plpgsql; which shows the following error: ERROR: language "plpgsql" already exists Commands are being run on the same database. Regards Peter ...
How I can round to nearest X minutes? Here's my attempt: DECLARE _stamp ALIAS FOR $1; -- timestamp _nearest ALIAS FOR $2; -- minutes (integer) _minutes decimal; _ret timestamp; BEGIN _ret := date_trunc('minute', _stamp); SELECT EXTRACT (minute FROM _ret)::integer INTO _minutes; IF (_minutes % _nearest < (_nearest / 2)) ...
Hello, we are still pretty new to Postgres and came from Microsoft Sql Server. We are wanting to write some stored procedures now. Well, after struggling to get something more complicated than a hello world to work in pl/pgsql, we decided it's better if we are going to learn a new language we might as well learn Python because we got th...
(This is similar to a question I asked earlier: http://stackoverflow.com/questions/2277731/porting-oracle-procedure-to-postgresql) I need to port: /* || The following private procedure will execute a dynamic pl/sql || statement passed to it. */ CREATE OR REPLACE FUNCTION DB_SHELL_UTIL_PKG.EXECUTE_STMT (stmt VARCHAR) IS v_num_...
Hi, Is there a way to update a table in database1 from a function in database2 (both databases are on the same server)? Basically cross database update in PostgreSQL. Function is executed by a trigger but it shouldn't matter. -= edit =- I know I can make it using DBLink but I would like to modify the source database as little as possi...
I have written the following function but it's isn't returning anything when I run it. Can somebody help identify the issue? CREATE OR REPLACE FUNCTION GenerateReadableRandomString ( len INT ) RETURNS varchar AS $$ DECLARE validchars VARCHAR; randomstr VARCHAR; randint INT; i INT; BEGIN validchars := 'ABCEFHJKLMNPRTWXY3478'; i := 0; ...
With SQL Server, I can execute code ad hoc T-SQL code with full procedural logic through SQL Server Management Studio, or any other client. I've begun working with PostgreSQL and have run into a bit of a difference in that PGSQL requires any logic to be embedded in a function. Is there a way to execute PL/PGSQL code without creating an...
I'm not even sure what this is called? But I'm trying to learn what the difference is between writing a function like this is in plpgsql: CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ .... $$ LANGUAGE plpgsql; vs CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $foo$ .... $foo$ LANGUAGE plpgsql; is there a diff...
Do the following scheme for my database: create sequence data_sequence; create table data_table { id integer primary key; field varchar(100); }; create view data_view as select id, field from data_table; create function data_insert(_new data_view) returns data_view as $$declare _id integer; _result data_view%rowtype; ...
I have a function in PostgreSQL / plpgsql with the following signature: CREATE OR REPLACE FUNCTION user_login(TEXT, TEXT) RETURNS SETOF _get_session AS $$ ... $$ Where _get_session is a view. The function works fine when calling it from phpPgAdmin, however whan I call it from PHP I get the following error: Warning: pg_query() [functi...