Tool for translation of Oracle PL/SQL into Postgresql PL/pgSQL
It there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language? ...
plpgsql
what are the advantages of using plpgsql in postgresql
Besides the syntactic sugar and expressiveness power what are the differences in runtime efficiency. I mean, plpgsql can be faster than, lets say plpythonu or pljava? Or are they all approximately equals? We are using stored procedures for the task of detecting nearly-duplicates records of people in a moderately sized database (around 1...
Session based global variable in Postgresql stored procedure?
In Oracle's PL/SQL I can create a session based global variable with the package definition. With Postgresql's PLpg/SQL, it doesn't seem possible since there are no packages, only independent procedures and functions. Here is the syntax for PL/SQL to declare g_spool_key as a global... CREATE OR REPLACE PACKAGE tox IS g_spool_key ...
How do I enable the PostgreSQL function profiler?
This took me a while to figure out and I found the answer on a foreign-language wiki a number of weeks ago, and it was very helpful, so I thought I would share. ...
How to delete table *or* view from PostgreSQL database?
Hello. I have a name of table or view in PostgreSQL database and need to delete in in single pgSQL command. How can i afford it? I was able to select form system table to find out if there any table with such a name but stuck with procedural part: SELECT count(*) FROM pg_tables where tablename='user_statistics'; ...
How can I perform an AND on an unknown number of booleans in postgresql?
I have a table with a foreign key and a boolean value (and a bunch of other columns that aren't relevant here), as such: CREATE TABLE myTable ( someKey integer, someBool boolean ); insert into myTable values (1, 't'),(1, 't'),(2, 'f'),(2, 't'); Each someKey could have 0 or more entries. For any given someKey, I need to know if a)...
PostgreSQL concatenation
I got this function to concat fields in my pgSQL-server: BEGIN IF acc IS NULL OR acc = '' THEN RETURN instr; ELSE RETURN acc || ';' || instr; END IF; END; It works great, but now I want this function to distinct equal entries. How can I do this? ...
How to access to fields of a record type in postgresql without knowing their names?
I'm programming a PL/pgSQL function with this: cols_to_select := according to a set of rules, we get the name of the columns FOR I IN EXECUTE 'SELECT '||cols_to_select||' FROM tabl' LOOP -- how to access to fields of record I without knowing their names? -- END LOOP; ...
Is there a way to know the name of a calling function in PL/PgSQL?
When I call a PL/PgSQL function (not a trigger) from another such function, is it possible to know the name or some kind of ID of a calling function? ...
Is it possible to create a table with a variable name in Postgre SQL?
Using PL/pgSQL or (some other mechanism), is it possible to create a table with a variable name? I would like to create multiple tables named table_1, table_2, table_3, etc... and it would be simpler if I could use a loop to create them, instead of explicitly creating each one. I suspect the answer to this is no, but I would like to co...
how to find what languages are loaded into EnterpriseDB?
How can I find what languages have been loaded into EnterpriseDB(PL/pgsql, SPL, Java)? EnterpriseDB is built on top of PostgreSQL if anyone knows of a way to find the loaded languages on PostgreSQL. It should work the same. ...
PostgreSQL return setof record ( virtual table )
Hi, i need a postgres function to return a virtual table ( like in oracle ) with custom content. The table would have 3 columns and an unknown amounts of rows.. i just couldn't find the correct syntax on the internet.. imagine this: CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric) RETURNS setof record AS DEC...
Better way to write PL/pgSQL?
Is there a decent IDE-like tool for writing and debugging PL/pgSQL functions, e.g. for writing stored procedures? I find it an exercise in frustration using pgAdmin III, because the error messages are often deeply cryptic and things have a habit of failing in mysterious ways. ...
How do I do large non-blocking updates in PostgreSQL?
I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written to or read during the update. I want to know if there is an easy way in the psql console to make these types of operations...
How do I get the primary key(s) of a table from Postgres via plpgsql?
Given a table name, how do I extract a list of primary key columns and their datatypes from a plpgsql function? ...
pgsql time diffrence?
How to write a query to find the time difference ? time format is like this 2009-08-12 02:59:59 i want to compare this time with 2009-08-12 02:59:10 how to check these two i want to return some row having the time difference is 30sec how to write a SQL statement ?? ...
Good resources for learning PL/pgSQL?
I've been looking around the net trying to find good resources for learning PostgreSQL's procedural programming language, PL/pgSQL. So far the only thing I've managed to dig up is the tutorial in the PostgreSQL documentation. While that is good, I've been looking for something more in-depth. Can you recommend anything? ...
How to check, if a value is an integer with plpgsql?
i am using this function in a trigger: CREATE OR REPLACE FUNCTION xx() RETURNS trigger AS $xx$ BEGIN INSERT INTO my_log (x, y, z) VALUES (NEW.x, NEW.y, current_setting('myvar.user')); RETURN NULL; END; $xx$ LANGUAGE plpgsql; now i would like to check, if 'myvar.user' is a valid integer, and if not, do anothe...
postgres update a date field when a boolean field is set to true
For the sake of the example, consider a table create table foo ( contents text NOT NULL, is_active boolean NOT NULL DEFAULT false, dt_active date ) I insert a record: insert into foo (contents) values ('bar') So far, so good. Later on, I now want to 'activate' the record: update foo set is_active = true What I would like t...
How can I re-use a function name as an out-parameter in PostgreSQL 8?
I have a function that has a very useful name: has_useful_state(param). I have a second function that will be returning a SETOF RECORDs of these results: CREATE OR REPLACE FUNCTION set_of_useful_things(param TEXT, OUT has_useful_state) RETURNS SETOF RECORD AS $_$ BEGIN SELECT some_key, COUNT(has_useful_state(some_key)) FROM .... ...