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? >

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

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_id, account_id, note_id, etc) > *- table2_y_b =* Table B

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

[GENERAL] PL/pgSQL debugger

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 EST and I haven't received a response yet I figured it ca

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

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

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, bar), 'baz' as baz from foobartable; Which should return som

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 '

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

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 'ERROR: invalid input syntax for type oid:' wh

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

[GENERAL] PL/PgSQL and pg_temp pseudo-schema

PL/PgSQL doesn't know the pg_temp meta schema in case you need to really work on that TEMP TABLE. At the moment I had to move those DDL/DML queries within a "plain" SQL function. Is this a feature or a bug? (Seriously, I mean! :-) TIA. -- Vincenzo Romano - NotOrAnd.IT Information Technologies --

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

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

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 fragile in that it needs to be updated any time a column is added to t

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

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

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 as $BODY$ DECLARE idcounter integer := 1; firstname t

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

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

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 be applied as a trigger on different tables, the function n

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

[GENERAL] PL/pgSQL debugger and PostgreSQL 9.2

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 somewhere else? Yours, Laurenz Albe -- Se

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

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

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 simple solution, but I'm damned if I can find it :-/ (Postgres v9.1.1 on Linux 64-bit) I'm trying to write a function which will :- 1. Take 3 input params;

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

[GENERAL] pl/pgsql and arrays[]

Some quetions about pl/pgsql and arrays[]. Is such constructions as: RETURN NEXT array[1]; OR SELECT val INTO array[1] FROM ...; Should not work? At least documentation about RETURN NEXT says: "RETURN NEXT expression;" I think array[1] is a valid expression. -- Maxim Boguk Senior Postgres

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

pgsql-general@postgresql.org> >Subject: [GENERAL] PL/pgSQL: SELECT INTO variables - no result > >I go through a series of SELECT INTO in a trigger function. > >SELECT INTO country_id id >FROM vcountry WHERE [...]; > >I might or o might not find a result. > >Next, I

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

I go through a series of SELECT INTO in a trigger function. SELECT INTO country_id id FROM vcountry WHERE [...]; I might or o might not find a result. Next, I try to find a region within this country, if found previously, and if not anywhere. SELECT INTO country_id, region_id country_fk,

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

[GENERAL] [PL/pgSQL] function call

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, vtriggertable text, vtriggerid text, vmessage text) RETURNS boolean AS $BODY$ BEGIN

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

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

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 result. If there is more than one result, I want to log the fact. EXAMPLE pseudo code select country_fk, region_fk, id fro

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

[GENERAL] PL/pgSQL trigger and sequence increment

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 entry timestamp. No two rows can h

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 >

[GENERAL] PL/PGSQL - dynamic variable names

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 exec_string := 'SELECT stuff_' || thing || '(''' || arg1

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, >>Suppos

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,

[GENERAL] Pl/pgsql function

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,time) from tablename .

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 >

  1   2   3   4   5   >