Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

Thanks Adrian... it's working ;)

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/06/2016 09:01 PM, Patrick B wrote: Ok so guys CREATE or REPLACE FUNCTION function_data_1() RETURNS SETOF bigint AS $$ declare row record; BEGIN [...] FOR row IN EXECUTE ' SELECT

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

Ok so guys CREATE or REPLACE FUNCTION function_data_1() RETURNS SETOF bigint AS $$ declare row record; BEGIN [...] FOR row IN EXECUTE ' SELECT t1.file_id, t1.path, t1.account_id FROM

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 11:59 PM, Patrick Baker wrote: > > > 2016-06-03 15:50 GMT+12:00 David G. Johnston : > >> On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker >> wrote: >> >>> >>> How can I make the function to gets the next 3 rows and not use the same >>> rows that have been used before? >>> >>

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Fri, Jun 3, 2016 at 3:16 PM, Adrian Klaver wrote: > On 06/03/2016 12:23 AM, Patrick Baker wrote: > >> >> >> -- Creating the backup table with the essential data >> INSERT INTO table2 (row.note_id, row.size, row.file_id, row.full_path) >> >> . >> >> Still not seeing what the

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/03/2016 12:23 AM, Patrick Baker wrote: -- Creating the backup table with the essential data INSERT INTO table2 (row.note_id, row.size, row.file_id, row.full_path) . Still not seeing what the JOIN to table3 t3 gets you? Any way the function works. I changed the

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

> > > > -- Creating the backup table with the essential data > INSERT INTO table2 (row.note_id, row.size, row.file_id, row.full_path) > > . > > Still not seeing what the JOIN to table3 t3 gets you? > > Any way the function works. > > I changed the function to use row.note_id, row.size, etc... t

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/02/2016 08:37 PM, Patrick Baker wrote: Hi guys, * The function works... All the data is updated as expected. However, when I call the function for the second time, it touches the rows that had already been touched by the previous call * It triplicate ( |LIMIT 3|

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

2016-06-03 15:50 GMT+12:00 David G. Johnston : > On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker > wrote: > >> >> How can I make the function to gets the next 3 rows and not use the same >> rows that have been used before? >> > ​WHERE migrated = 0 > ​ > ​David J. > > > lol... that's right David J

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker wrote: > > How can I make the function to gets the next 3 rows and not use the same > rows that have been used before? > ​WHERE migrated = 0 ​ ​David J.

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

Hi guys, - The function works... All the data is updated as expected. However, when I call the function for the second time, it touches the rows that had already been touched by the previous call - It triplicate ( LIMIT 3 ) the records. *Question:* How can I make the fun

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

I did: CREATE or REPLACE FUNCTION function_1_data() RETURNS INTEGER AS $$ declare row record; BEGIN -- copying the data to the backup table (not the blobs) FOR row IN EXECUTE ' SELECT t1.file_id FROM table1_n_b t1

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

> > >> > > Why are you joining to table3_nb? > You do not use any fields from it. > > How do you know what data in table1_n_b to get? > I see this grabbing the same information over and over again. SELECT * INTO table3_n_b FROM ( SELECT account_id, note_id, file_id FROM (

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/02/2016 02:03 PM, Patrick Baker wrote: 2016-06-03 2:10 GMT+12:00 David G. Johnston mailto:david.g.johns...@gmail.com>>: Hi David. The SQLs inside the function works I'm just having problem about limiting the query to the number of rows I want, and also, to teach the update SQL t

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 5:03 PM, Patrick Baker wrote: > > > 2016-06-03 2:10 GMT+12:00 David G. Johnston : > >> On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker >> wrote: >> >>> > It's all working, except the LIMIT... if possible can you please give >>> me an example of that LIMIT in some of tho

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

2016-06-03 2:10 GMT+12:00 David G. Johnston : > On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker > wrote: > >> It's all working, except the LIMIT... if possible can you please give >> me an example of that LIMIT in some of those queries? >> >> ​​ > You also should use ORDER BY when using LIMIT

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker wrote: > >>> It's all working, except the LIMIT... if possible can you please give > me an example of that LIMIT in some of those queries? > > ​​ You also should use ORDER BY when using LIMIT and OFFSET; though depending on the setup it could be omit

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/01/2016 10:04 PM, Patrick Baker wrote: I maybe be missing it, but I see no LIMIT in the function. I do see OFFSET and it looks backwards to me?: || $1 ||' offset '|| https://www.postgresql.org/docs/9.5/static/sql-select.html LIMIT Clause The LIMIT clause consi

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

> > >> > I maybe be missing it, but I see no LIMIT in the function. > > I do see OFFSET and it looks backwards to me?: > > || $1 ||' offset '|| > > https://www.postgresql.org/docs/9.5/static/sql-select.html > > LIMIT Clause > > The LIMIT clause consists of two independent sub-clauses: > > LIMIT { c

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/01/2016 05:10 PM, Patrick Baker wrote: Hi guys, I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS... I have four tables: *- original_table1_b =* Original table, where the BLOBS are *- table1_n_b =* Table where everything related to the BLOBS is stored (file_i

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Wednesday, June 1, 2016, Patrick Baker wrote: > > >> >> ​I'd suggest you setup a test environment with some unimportant data on a >> non-production machine and try it yourself. >> ​ >> ​​ >> >> David J. >> ​ >> >> >> > Thanks.. but if I'm asking the list that's because I'm already testing it >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

> > > ​I'd suggest you setup a test environment with some unimportant data on a > non-production machine and try it yourself. > ​ > ​​ > > David J. > ​ > > > Thanks.. but if I'm asking the list that's because I'm already testing it and it's not working... ;) Patrick

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Wed, Jun 1, 2016 at 8:10 PM, Patrick Baker wrote: > Hi guys, > > I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS... > > ​[...] ​ > When I will call the function: *select function_1_name(5000) or **select > function_1_name(15000)* will it respect the limited by the rows? >

Re: [GENERAL] PL/pgSQL debugger

Dane Foster wrote: > I googled "plpgsql debugger" and eventually ended up at > http://pgfoundry.org/projects/edb-debugger/, > where the latest release is almost 8 years old. I am aware that this is not > an EDB forum but given > that I sent them an e-mail at 9AM EST and I haven't received a respo

Re: [GENERAL] PL/pgSQL debugger

On Fri, Jan 29, 2016 at 4:22 PM, Melvin Davidson wrote: > You did not indicate which O/S you are using, but here are the detailed > instructions for Windows. > > > http://blog.databasepatterns.com/2014/01/installing-debugger-for-pgadmin-on.html > > Note that the most important part is to load the

Re: [GENERAL] PL/pgSQL debugger

You did not indicate which O/S you are using, but here are the detailed instructions for Windows. http://blog.databasepatterns.com/2014/01/installing-debugger-for-pgadmin-on.html Note that the most important part is to load the .dll in postgresql.conf. shared_preload_libraries = '$libdir/plugin_

Re: [GENERAL] PL/pgSQL debugger

Hi 2016-01-29 21:52 GMT+01:00 Dane Foster : > Hello, > > I googled "plpgsql debugger" and eventually ended up at > http://pgfoundry.org/projects/edb-debugger/, where the latest release is > almost 8 years old. I am aware that this is not an EDB forum but given that > I sent them an e-mail at 9AM

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 12:00 PM, Sven Geggus wrote: > David G. Johnston wrote: > > > Assuming you are on 9.3+ what you want to use is LATERAL > > OK, how is such a query supposed to look like? > > assuming "select myfunc(col1,col2) from mytable" works as the inner select? > ​Syntax, descriptio

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

David G. Johnston wrote: > Assuming you are on 9.3+ what you want to use is LATERAL OK, how is such a query supposed to look like? assuming "select myfunc(col1,col2) from mytable" works as the inner select? Sven -- Software patents are the software project equivalent of land mines: Each desi

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Sven Geggus wrote: > Using your suggestion the desired two columns are generated, but I consider > this a little bit ugly: > > mydb=> WITH exec_func AS ( select myfunc(col1,col2) from mytable ) > SELECT (exec_func.myfunc).* FROM exec_func; > HINWEIS: called with parms foo,bar: text1 value1 > HIN

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 10:31 AM, Sven Geggus wrote: > David G. Johnston wrote: > > WITH exec_func AS ( SELECT myfunc(col1,col2) FROM mytable ) > > SELECT (exec_func.myfunc).* FROM exec_func; > > > > This relies on the fact that currently a CTE introduces an optimization > > barrier. > > Hm, let

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

David G. Johnston wrote: > WITH exec_func AS ( SELECT myfunc(col1,col2) FROM mytable ) > SELECT (exec_func.myfunc).* FROM exec_func; > > This relies on the fact that currently a CTE introduces an optimization > barrier. Hm, let me summarize. My function seems to work as expected and is only call

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 9:52 AM, Sven Geggus wrote: > Raymond O'Donnell wrote: > > >> mydb=> select myfunc('foo','bar'); > > > > You need to do: > > > >select * from myfunc('foo','bar'); > > This has been a misguided example. Reality should more likely look like > this: > > select myfunc(col

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Raymond O'Donnell wrote: >> mydb=> select myfunc('foo','bar'); > > You need to do: > >select * from myfunc('foo','bar'); This has been a misguided example. Reality should more likely look like this: select myfunc(col1,col2) from mytable; And it would of course be undesired if myfunc woul

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 9:32 AM, Sven Geggus wrote: > David G. Johnston wrote: > > > Look at the "returns table (col1 type, col2 type)" form. > > If I got this right "returns table" is not what I want as I need to select > from my function as a virtual table in this case. > ​Yes, I mis-read you

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Pavel Stehule wrote: > CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text) > RETURNS SETOF record > LANGUAGE plpgsql > AS $function$ > BEGIN > RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id); > RETURN; > END; > $function$ I'm afraid I will almos

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

David G. Johnston wrote: > Look at the "returns table (col1 type, col2 type)" form. If I got this right "returns table" is not what I want as I need to select from my function as a virtual table in this case. Regards Sven -- "Thinking of using NT for your critical apps?

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thursday, June 18, 2015, Chris Travers wrote: > > > Select (myfunc('foo','bar')).*; > > > This should be avoided. Use lateral instead,or a cte a/o offset 0. My_func is evaluated twice (once per column) if called this way > > Or > Select * from myfunc('foo','bar'); > This is ok David J.

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

"Raymond O'Donnell" writes: > On 18/06/2015 13:36, Sven Geggus wrote: >> I would like to be able to do something like this: >> >> select myfunc('foo','bar'); >> or >> select myfunc(foo, bar) from foobartable; >> or even >> select myfunc(foo, bar), 'baz' as baz from foobartable; > You need to do:

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015 at 7:50 AM, Chris Travers wrote: > > On Thu, Jun 18, 2015, 14:38 Sven Geggus wrote: > > Hello, > > I supose this is simple, but I did not find a solution in the documentation. > > Because you already are returning 2 columns. > > I would like to be able to do something like th

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On 18/06/2015 13:36, Sven Geggus wrote: > Hello, > > I supose this is simple, but I did not find a solution in the documentation. > > I would like to be able to do something like this: > > select myfunc('foo','bar'); > or > select myfunc(foo, bar) from foobartable; > or even > select myfunc(foo,

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thu, Jun 18, 2015, 14:38 Sven Geggus wrote: Hello, I supose this is simple, but I did not find a solution in the documentation. Because you already are returning 2 columns. I would like to be able to do something like this: select myfunc('foo','bar'); or select myfunc(foo, bar) from foob

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

Hi CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text) RETURNS SETOF record LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id); RETURN; END; $function$ postgres=# SELECT a,b FROM fx(4); ┌──┬──┐ │ a │

Re: [GENERAL] PL/pgSQL: How to return two columns and multiple rows

On Thursday, June 18, 2015, Sven Geggus wrote: > Hello, > > I supose this is simple, but I did not find a solution in the > documentation. > > I would like to be able to do something like this: > > select myfunc('foo','bar'); > or > select myfunc(foo, bar) from foobartable; > or even > select myf

Re: [GENERAL] PL\pgSQL 'ERROR: invalid input syntax for type oid:' [PostgreSQL 9.3.6 and 9.4]

Thanks Adrian! Changing the declaration row_data to be of type RECORD (rather than pg_catalog.pg_class%ROWTYPE) resolved the error :) - Will *Will J Dunn* *willjdunn.com * On Thu, Apr 16, 2015 at 4:36 PM, Adrian Klaver wrote: > On 04/16/2015 07:52 AM, William Dunn wrote

Re: [GENERAL] PL\pgSQL 'ERROR: invalid input syntax for type oid:' [PostgreSQL 9.3.6 and 9.4]

On 04/16/2015 07:52 AM, William Dunn wrote: Hello list, I am creating a plpgsql procedure in Postgres 9.4 (also testing in 9.3.6) to move all of the tables that are not in a default tablespace (pg_default, pg_global, or 0) into the tablespace pg_default. However when it executes I get an error '

Re: [GENERAL] PL/PgSQL and pg_temp pseudo-schema

Take note. Temp tables are only visable, available to the sessions that created them. You cannot access them from a different session. Is that the problem you experience> If not, please provide a specific example of what your problem is. On Fri, Feb 20, 2015 at 3:34 AM, Pavel Stehule wrote: > Hi

Re: [GENERAL] PL/PgSQL and pg_temp pseudo-schema

Hi It is little bit strange, it works for me postgres=# create temp table foo(a int); CREATE TABLE postgres=# do $$ begin insert into foo values(10); end $$; -- plpgsql DO postgres=# select * from foo; a 10 (1 row) Regards Pavel 2015-02-20 9:07 GMT+01:00 Vincenzo Romano : > PL/PgSQL

Re: [GENERAL] pl/pgsql trigger function - compare *most* columns in NEW vs. OLD

On Thu, Dec 18, 2014 at 12:16 PM, Mike Blackwell wrote: > Is there a simple notation for comparing most columns in the new and old > records in a pl/pgsql trigger function? Something like > > (new.b, new.c, new.d) = (old.b, old.c, old.d) > > works to compare all the columns except 'a', but is fra

Re: [GENERAL] PL/pgSQL Copy data from one table to another

George Ant wrote > Hey Guys, > > Thank you for your replies! Your suggestions worked fine!! :) Also my code > looks a lot cleaner now! > > Kind Regards, > George Ant. Have you considered just creating a view, or even retrieval functions, the generate these "object forms" on the fly instead of

Re: [GENERAL] PL/pgSQL Copy data from one table to another

Hey Guys, Thank you for your replies! Your suggestions worked fine!! :) Also my code looks a lot cleaner now! Kind Regards, George Ant. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-Copy-data-from-one-table-to-another-tp5790663p5791086.html Sent from the

Re: [GENERAL] PL/pgSQL Copy data from one table to another

On 2014-02-05 10:36, ChoonSoo Park wrote: On Wed, Feb 5, 2014 at 7:53 AM, George Ant mailto:g.antonopoulos...@gmail.com>> wrote: Hey Guys, I am trying to copy data from one table to another using plpgsql. The two tables have different structure cause the new one is object-table

Re: [GENERAL] PL/pgSQL Copy data from one table to another

On Wed, Feb 5, 2014 at 7:53 AM, George Ant wrote: > Hey Guys, > > I am trying to copy data from one table to another using plpgsql. The two > tables have different structure cause the new one is object-table. My > function is this : > > CREATE OR REPLACE FUNCTION copy_customers() RETURNS integer a

Re: [GENERAL] [PL/PGSQL] column name substitution in PG8.4

Le mercredi 03 octobre 2012 à 22:15 -0500, Merlin Moncure a écrit : > Really though, upgrading the database makes the most sense IMO. 8.4 > indicates that your customer is probably sticking with the rhel stock > packaging Could be Debian Stable too... -- Vincent Veyron http://marica.fr/ Ges

Re: [GENERAL] [PL/PGSQL] column name substitution in PG8.4

On Wed, Oct 3, 2012 at 8:15 AM, Léon Melis wrote: > For some of my customers I wrote a PL/PGSQL function that stores the > difference between an OLD en NEW record when updating a record. This system > can be applied as a trigger on the table the customer likes to audit. > Because the function can

Re: [GENERAL] PL/pgSQL debugger and PostgreSQL 9.2

Is there link to download pldbgapi.dll on postgresql 9.2 x64 on windows Thanks in advance Tuan Hoang Anh On Fri, Sep 14, 2012 at 9:29 PM, Albe Laurenz wrote: > Fabrízio de Royes Mello wrote: > >> Has it moved somewhere else? > > > Look at the pldebugger git repository in [1]. > > Thanks! > > La

Re: [GENERAL] PL/pgSQL debugger and PostgreSQL 9.2

Fabrízio de Royes Mello wrote: >> Has it moved somewhere else? > Look at the pldebugger git repository in [1]. Thanks! Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PL/pgSQL debugger and PostgreSQL 9.2

2012/9/14 Albe Laurenz > Dear Dave, dear Korry, > > I tried to build the debugger > (http://pgfoundry.org/projects/edb-debugger/) > with PostgreSQL 9.2 and it failed. > > The code seems to have bitrotted. > The last commit was 16 months ago. > > Do you still maintain the project? > Has it moved s

Re: [GENERAL] PL/pgSQL - Help or advice please on using unbound cursors

Yep, that fixed it. Many thanks for the pointer. From a performance point of view, I did look at using MOVE rather than FETCH before I call UPDATE as I don't actually need to read the data before applying the update. However in this situation, the ROW_COUNT is always zero and can't be used whi

Re: [GENERAL] PL/pgSQL - Help or advice please on using unbound cursors

Hello dynamic SQL has not impact on FOUND variable - use GET DIAGNOSTICS varname = ROW_COUNT instead. Regards Pavel Stehule 2012/7/23 Andrew Hastie : > Hi all, > > Apologies if the answer to my question is "obvious", but I'm fairly new to > writing functions in PG. I think my problem is has a

Re: [GENERAL] pl/pgsql and arrays[]

2011/12/5 Tom Lane : > Pavel Stehule writes: >> return next in function that returns composite type needs a composite >> variable. Other cases are not supported there. > > Plain "return" has the same limitation, but this really ought to be > fixed sometime.  Composite types have been getting close

Re: [GENERAL] pl/pgsql and arrays[]

Pavel Stehule writes: > return next in function that returns composite type needs a composite > variable. Other cases are not supported there. Plain "return" has the same limitation, but this really ought to be fixed sometime. Composite types have been getting closer and closer to first-class st

Re: [GENERAL] pl/pgsql and arrays[]

2011/12/5 Maxim Boguk : > > > On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule > wrote: >> >> Hello >> >> it work on my pc >> >> postgres=# \sf fx >> CREATE OR REPLACE FUNCTION public.fx() >>  RETURNS SETOF integer >>  LANGUAGE plpgsql >> AS $function$ declare g int[] = '{20}'; >> begin >>  return ne

Re: [GENERAL] pl/pgsql and arrays[]

On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule wrote: > Hello > > it work on my pc > > postgres=# \sf fx > CREATE OR REPLACE FUNCTION public.fx() > RETURNS SETOF integer > LANGUAGE plpgsql > AS $function$ declare g int[] = '{20}'; > begin > return next g[1]; > return; > end; > $function$ > post

Re: [GENERAL] pl/pgsql and arrays[]

Hello it work on my pc postgres=# \sf fx CREATE OR REPLACE FUNCTION public.fx() RETURNS SETOF integer LANGUAGE plpgsql AS $function$ declare g int[] = '{20}'; begin return next g[1]; return; end; $function$ postgres=# select fx(); fx 20 (1 row) regards Pavel Stehule 2011/12/5 Maxi

Re: [GENERAL] PL/pgSQL: SELECT INTO variables - no result

On Thu, 10 Nov 2011 17:46:47 -0500 "David Johnston" wrote: >-Original Message- >From: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tarlika >Elisabeth Schmitz >Sent: Thursday, November 10, 2011 5:18 PM >To: pgsql-general@postgresql.org> >Subj

Re: [GENERAL] [PL/pgSQL] function call

On Mon, 31 Oct 2011 09:41:40 -0500 Merlin Moncure wrote: >On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz > wrote: >> I have created a function log_insert(), which is simply a shorthand >> for an INSERT table and which I want to call from various trigger >> functions. >> >> CREATE OR R

Re: [GENERAL] [PL/pgSQL] function call

2011/10/31 Merlin Moncure : > On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz > wrote: >> I have created a function log_insert(), which is simply a shorthand for >> an INSERT table and which I want to call from various trigger functions. >> >> CREATE OR REPLACE FUNCTION log_insert(vseve

Re: [GENERAL] [PL/pgSQL] function call

On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz wrote: > I have created a function log_insert(), which is simply a shorthand for > an INSERT table and which I want to call from various trigger functions. > > CREATE OR REPLACE FUNCTION log_insert(vseverity text, vtrigger text, > vtrigger

Re: [GENERAL] PL/pgSQL trigger and sequence increment

jon...@xmission.com writes: > CREATE UNIQUE INDEX one_entry_per_submitter_per_month ON table_entry > (submitter_id , date_trunc('month',entry_timestamp)); > runs into > ERROR: functions in index expression must be marked IMMUTABLE. > If I'm reading this correctly, date_trunc is not IMMUTABLE an

Re: [GENERAL] PL/pgSQL trigger and sequence increment

Seems like you would be a lot better off enforcing this with a unique index on (submitter_id, date_trunc('month',entry_timestamp)). The above not only doesn't provide any feedback, it's got serious race-condition problems. Unfortunately, it didn't work. CREATE UNIQUE INDEX one_entry_per_submit

Re: [GENERAL] PL/pgSQL trigger and sequence increment

Seems like you would be a lot better off enforcing this with a unique index on (submitter_id, date_trunc('month',entry_timestamp)). The above not only doesn't provide any feedback, it's got serious race-condition problems. I'll take a look at using an index to do this. The trigger is an ugly

Re: [GENERAL] PL/pgSQL: SELECT INTO only if result count = 1

On Sat, 27 Aug 2011 18:36:14 -0400 Tom Lane wrote: >Tarlika Elisabeth Schmitz writes: >> SELECT INTO >> country_id, region_id, town_id >> country_fk, region_fk, id >> FROM town >> WHERE ...; > >> GET DIAGNOSTICS cnt = ROW_COUNT; >> RAISE DEBUG 'COUNT %', cnt; > >> always returns 1 > >Yeah. B

Re: [GENERAL] PL/pgSQL: SELECT INTO only if result count = 1

Tarlika Elisabeth Schmitz writes: > SELECT INTO > country_id, region_id, town_id > country_fk, region_fk, id > FROM town > WHERE ...; > GET DIAGNOSTICS cnt = ROW_COUNT; > RAISE DEBUG 'COUNT %', cnt; > always returns 1 Yeah. By default, SELECT INTO just fetches one row and stops; it doesn't

Re: [GENERAL] PL/pgSQL: SELECT INTO only if result count = 1

On Sat, 27 Aug 2011 18:45:42 -0300 Osvaldo Kussama wrote: >2011/8/27, Tarlika Elisabeth Schmitz >: >> Hello, >> >> In a PL/pgSQL trigger function, I try to select a record from table >> "town" below. >> >> I am only interested in the result if the returned result set >> contains exactly one resul

Re: [GENERAL] PL/pgSQL trigger and sequence increment

jon...@xmission.com writes: > Greetings. I noticed an interesting behavior when using a PL/pgSQL > trigger. I'm running PostgreSQL 8.3. The trigger function checks a > newly inserted or updated row for a type of uniqueness. Specifically, > each row in the table has a submitter id and an e

Re: [GENERAL] PL/PGSQL - dynamic variable names

Ben Carbery wrote: Hm, "a" isn't a variable,it's the value of stuff[1]. It's both.. DECLARE a integer; b integer; The point is I want a dynamically named variable. Here I've named them the same as stuff[i] but they can be anything provided it is a different variable name on

Re: [GENERAL] PL/PGSQL - dynamic variable names

Actually there is a section in the manual on this problem: http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST On 29 June 2011 11:41, Ben Carbery wrote: > > Hm, "a" isn't a variable,it's the value of stuff[1]. > > > It's both.. > > DECLARE > a integer; >

Re: [GENERAL] PL/PGSQL - dynamic variable names

> Hm, "a" isn't a variable,it's the value of stuff[1]. It's both.. DECLARE a integer; b integer; The point is I want a dynamically named variable. Here I've named them the same as stuff[i] but they can be anything provided it is a different variable name on each loop iteration. The manu

Re: [GENERAL] PL/PGSQL - dynamic variable names

On 06/28/2011 06:29 PM, Ben Carbery wrote: > Hi, > > I am trying to EXECUTE .. INTO a variable that I want to be > dynamically named. > > stuff := '{a,b,c,d}'; > > FOR i IN 1..4 LOOP > thing := stuff[i]; > > -- stuff_a, stuff_b etc are functions - substitution works here >

Re: [GENERAL] Pl/pgsql function

>>From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Nick Raj >>Sent: Saturday, June 04, 2011 10:04 AM >>To: pgsql-general@postgresql.org >>Subject: [GENERAL] Pl/pgsql function >> >>Second thing, >>Suppose i defined a function test as >> >>select test

Re: [GENERAL] Pl/pgsql function

Nick Raj wrote: I am implementing some pl/pgsql functions. Is there any way to change the input for example- I got some value by $1. I want to modify this value (means split that value), Can we do this and how? Second thing, Suppose i defined a function test as select test('geom',the_geom,

Re: [GENERAL] pl/pgSQL variable substitution

Jeremy Palmer writes: > When I have a query that uses DESC reserved word within the function the > following variable substitution occurs: > ERROR: syntax error at or near "$1" > LINE 1: SELECT foo.bar, foo."desc" FROM foo."desc" ORDER BY foo."desc" > $1 >

Re: [GENERAL] pl/pgSQL variable substitution

On 17 Feb 2011, at 5:33, Jeremy Palmer wrote: > Hi, > > I'm creating a pl/pgSQL function that returns a table that has a column name > which is the same as a PostgreSQL reserved. In the below example a have > returning table with a column called 'desc': > > CREATE OR REPLACE FUNCTION bad_func

Re: [GENERAL] pl/pgSQL variable substitution

Hello you cannot use a variable as column name or table name. It's not possible, because it can change execution plan and it isn't allowed. Use a dynamic SQL instead. RETURN QUERY EXECUTE 'SELECT foo.bar, foo.' || quote_ident("desc") || ' FROM foo ORDER BY foo.' || quote_ident("desc") || ' DE

Re: [GENERAL] pl/pgsql editor and postgres developemnt tool

On 10/08/2010 13:54, atul.g...@globaldatapoint.com wrote: > Hi Ray, > > Thanks for reply. Are you using any editor for code formatting? I use the query editor in PgAdmin, and it works fine for me - syntax highlighting, auto-indent, keyboard shortcuts for indentation and block commenting, etc etc.

Re: [GENERAL] pl/pgsql editor and postgres developemnt tool

Hi Ray, Thanks for reply. Are you using any editor for code formatting? Regards, Atul Goel -Original Message- From: Raymond O'Donnell [mailto:r...@iol.ie] Sent: 10 August 2010 10:30 To: Atul Goel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pl/pgsql editor and pos

Re: [GENERAL] pl/pgsql editor and postgres developemnt tool

On 10/08/2010 09:39, atul.g...@globaldatapoint.com wrote: > Hi All, > > > > I am looking for a good tool for pl/pgsql / postgres developemnt tool. > Something like Toad for Oracle. > pgAdmin together with the pl/pgsql debugger plug-in are a pretty good combination by all accounts. Ray. --

Re: [GENERAL] PL/pgSQL nested functions

> Are there any plans to allow PL/pgSQL functions to be nested like Oracle > allows with PL/SQL procedures? > > If not, what are the best ways to convert PL/SQL nested procedures to > PL/pgSQL? > > PostgreSQL plus advanced server (which is a proprietary derivative of postgres) has oracle compatibi

Re: [GENERAL] PL/pgSQL nested functions

On Thu, Jun 10, 2010 at 2:23 PM, wrote: > > Are there any plans to allow PL/pgSQL functions to be nested like Oracle > allows with PL/SQL procedures? > > If not, what are the best ways to convert PL/SQL nested procedures to > PL/pgSQL? Well you can call one function from another, so I'd do that.

Re: [GENERAL] PL/pgSQL nested functions

On 10/06/2010 21:23, jim.g...@bull.com wrote: > > Are there any plans to allow PL/pgSQL functions to be nested like Oracle > allows with PL/SQL procedures? I don't know the answer to your question, but I'm curious - why would you want to nest functions? Ray. -- Raymond O'Donnell :: Galway ::

Re: [GENERAL] PL/pgSQL nested functions

d O'Donnell" 06/10/2010 01:33 PM Please respond to r...@iol.ie To jim.g...@bull.com cc pgsql-general@postgresql.org Subject Re: [GENERAL] PL/pgSQL nested functions On 10/06/2010 21:23, jim.g...@bull.com wrote: > > Are there any plans to allow PL/pgSQL functions to be neste

Re: [GENERAL] PL/pgSQL & OVERLAPS operator (SOLVED!)

--- On Tue, 3/23/10, Tom Lane wrote: > Yeah --- that SELECT will result in no change to the > variables, ie, > they'll still be NULL.  So the OVERLAPS always fails. Tom & Andreas, I thank you for your help. Renaming the variables solved the problem. :-) Regards, Tuo -- Sent via pgsql-gener

Re: [GENERAL] PL/pgSQL & OVERLAPS operator

"A. Kretschmer" writes: > In response to Tuo Pe : >> CREATE OR REPLACE FUNCTION TryOverlap(text, text) RETURNS boolean AS $$ >> DECLARE >> ts_start timestamp with time zone := CAST ($1 AS TIMESTAMP WITH TIME ZONE); >> ts_end timestamp with time zone := CAST ($2 AS TIMESTAMP WITH TIME ZONE); >> alk

Re: [GENERAL] PL/pgSQL & OVERLAPS operator

--- On Tue, 3/23/10, A. Kretschmer wrote: > You have alku and loppu as variable and as table-column, > that's a bad > idea, maybe that's an error, i'm not sure. > > Btw.: you can use the PERIOD-datatype: I will look into it. Thanks for the tip. Tuo -- Sent via pgsql-general mailing

Re: [GENERAL] PL/pgSQL & OVERLAPS operator

In response to Tuo Pe : > Hello! > > I am teaching myself PL/pgSQL. I am trying to write a function that tests > whether two time periods overlap. I want to test the function parameters > against these two values in "overlaptest" table: > > select * from overlaptest; > id |alku

Re: [GENERAL] pl/pgsql string combining

Dear Mr. Stehule Thankyou for your super prompt (came to my mailbox less then 2 minutes since my post) enlightment. I'll try it Sincerely -bino- Pavel Stehule wrote: Hello NULL and any is NULL. So you have to use "coalesce" function. like NEW.prefix = ctrcode || coalesce(NEW.code, '');

Re: [GENERAL] pl/pgsql string combining

2009/12/16 Bino Oetomo : > Dear All > > I have 2 table : > 1. hotel_pbx_country > 2. hotel_pbx_area > > Country is one2many to area > Area have a field called "prefx" > > The "prefx" field is auto filled by country.code and area.code > and for that purpose, i created trigger and function >

Re: [GENERAL] PL/pgSQL infinite loop in "UPDATE/INSERT" example

Michael Brown writes: > I believe that Example 38-2 ("Exceptions with UPDATE/INSERT") in the PL/pgSQL > documentation suffers from a race condition leading to a potential infinite > loop when running at isolation level SERIALIZABLE. You're right, it's not designed to work in serializable mode.

Re: [GENERAL] pl/pgsql loop thru columns names

Dilyan Berkovski wrote: Hi All, I have a nasty table with many repeating columns of the kind port_ts_{i}_, where {i} is from 0 to 31, and could be 3 different words. I have made a pl/pgsql function that checks those columns from port_ts_1_status to port_ts_31_status and counts something, howe

  1   2   3   4   >