Re: [GENERAL] plpgsql function with offset - Postgres 9.1

On 2017-06-16 10:19:45 +1200, Patrick B wrote: > 2017-05-29 19:27 GMT+12:00 Albe Laurenz : > Patrick B wrote: > > I am running a background task on my DB, which will copy data from > tableA > to tableB. For > > that, I'm writing a PL/PGSQL function which basically needs to do the >

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

On Thu, Jun 15, 2017 at 3:49 PM, Patrick B wrote: > 2017-06-16 10:35 GMT+12:00 David G. Johnston : > >> On Thu, Jun 15, 2017 at 3:19 PM, Patrick B >> wrote: >> >>> 2017-05-29 19:27 GMT+12:00 Albe Laurenz : >>> Patrick B wrote: > I am running a background task on my DB, which will copy

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

2017-06-16 10:35 GMT+12:00 David G. Johnston : > On Thu, Jun 15, 2017 at 3:19 PM, Patrick B > wrote: > >> 2017-05-29 19:27 GMT+12:00 Albe Laurenz : >> >>> Patrick B wrote: >>> > I am running a background task on my DB, which will copy data from >>> tableA to tableB. For >>> > that, I'm writing a

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

On Thu, Jun 15, 2017 at 3:19 PM, Patrick B wrote: > 2017-05-29 19:27 GMT+12:00 Albe Laurenz : > >> Patrick B wrote: >> > I am running a background task on my DB, which will copy data from >> tableA to tableB. For >> > that, I'm writing a PL/PGSQL function which basically needs to do the >> follow

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

2017-05-29 19:27 GMT+12:00 Albe Laurenz : > Patrick B wrote: > > I am running a background task on my DB, which will copy data from > tableA to tableB. For > > that, I'm writing a PL/PGSQL function which basically needs to do the > following: > > > > > > 1.Select the data from tableA > > 2.

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

Patrick B wrote: > I am running a background task on my DB, which will copy data from tableA to > tableB. For > that, I'm writing a PL/PGSQL function which basically needs to do the > following: > > > 1.Select the data from tableA > 2.The limit will be put when calling the function > 3.

[GENERAL] plpgsql function with offset - Postgres 9.1

Hi guys, I am running a background task on my DB, which will copy data from tableA to tableB. For that, I'm writing a PL/PGSQL function which basically needs to do the following: 1. Select the data from tableA 2. The limit will be put when calling the function 3. insert the selected dat

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 1:17 PM, Patrick B wrote: > > > 2. To call the function, I have to login to postgres and then run: select > logextract(201612015, 201612015); > How can I do it on cron? because the dates will be different every time. > PostgreSQL already knows what date today is. Why doe

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On 12/14/2016 05:56 PM, Lucas Possamai wrote: ERROR: column "date_start" does not exist Patrick Patrick*** - trying on SQL fiddle i got that error when executing what Adrian suggested. Yeah, it was my turn not to be paying attention. It has been that sort of day and I guess I co

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:54 GMT+13:00 Lucas Possamai : > > > 2016-12-15 14:34 GMT+13:00 Adrian Klaver : > >> On 12/14/2016 05:19 PM, Patrick B wrote: >> >> Reading the suggestions might help:) >> >> Another try: >> >> CREATE or REPLACE FUNCTION l_extract(date_start text, date_end text)) >> >> RETURNS

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

> > ERROR: column "date_start" does not exist > > > Patrick > Patrick*** - trying on SQL fiddle i got that error when executing what Adrian suggested.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:34 GMT+13:00 Adrian Klaver : > On 12/14/2016 05:19 PM, Patrick B wrote: > > Reading the suggestions might help:) > > Another try: > > CREATE or REPLACE FUNCTION l_extract(date_start text, date_end text)) > > RETURNS void AS $$ > > > begin > > execute ' > >

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wednesday, December 14, 2016, Patrick B wrote: > > ' || date_start || ' > > AND > > ' || date_end || ' > > Results in this > BETWEEN > > 2016-12-15 > > AND > > 20160901 > > Compared to this > '2016-12-15' > > AND > >

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On 12/14/2016 05:19 PM, Patrick B wrote: 2016-12-15 14:00 GMT+13:00 David G. Johnston mailto:david.g.johns...@gmail.com>>: On Wed, Dec 14, 2016 at 5:12 PM, rob stone mailto:floripa...@gmail.com>>wrote: On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: > On Wed,

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

I presume you point at me. Keep the record straight. I got mad not for the help but for the high horse attitude. We all have good and bad. No one is perfect and no one deserves this crap Sent from my iPhone > On Dec 14, 2016, at 7:19 PM, Patrick B wrote: > > > > 2016-12-15 14:00 GMT+13:00 D

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:00 GMT+13:00 David G. Johnston : > On Wed, Dec 14, 2016 at 5:12 PM, rob stone wrote: > >> >> On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: >> > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B >> > wrote: >> > > ERROR: function logextract(integer, integer) does not exist >

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 5:12 PM, rob stone wrote: > > On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: > > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B > > wrote: > > > ERROR: function logextract(integer, integer) does not exist > > > LINE 1: select logextract(20160901,20161001); > > >

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B > wrote: > > ERROR:  function logextract(integer, integer) does not exist > > LINE 1: select logextract(20160901,20161001); > > > > So change the constants you are passing into your function

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 4:49 PM, Patrick B wrote: > ERROR: function logextract(integer, integer) does not exist > > LINE 1: select logextract(20160901,20161001); > So change the constants you are passing into your function to text (i.e., surrounding them with single quotes) so it matches the new

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

2016-12-15 10:40 GMT+13:00 Adrian Klaver : > On 12/14/2016 01:30 PM, Patrick B wrote: > >> 1. Why when I run the function manually I get this error? >> >> select logextract(201612015, 201612015); >> >> ERROR: operator does not exist: timestamp without time

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On 12/14/2016 01:30 PM, Patrick B wrote: 1. Why when I run the function manually I get this error? select logextract(201612015, 201612015); ERROR: operator does not exist: timestamp without time zone >= integer LI

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 2:17 PM, Patrick B wrote: > > As you can see, I select a date. So in December, the date will be: *BETWEEN > '201612015' AND '201601015'*, for example. > > ​That is an unusual timestamp value...what's the 5 for?​ (I've figured this out...but its still unusual) > > > 1. Why

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

> > 1. Why when I run the function manually I get this error? >> >> select logextract(201612015, 201612015); >> >> ERROR: operator does not exist: timestamp without time zone >= >> integer >> >> LINE 13: BETWEEN >> > > The answer is above. Look at yo

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On 12/14/2016 01:17 PM, Patrick B wrote: Hi, I've got this query, that I manually run it once a month: SELECT uuid, clientid), * FROM logging WHERE logtime BETWEEN '201611015' AND '201612015' As you can see, I select

[GENERAL] Plpgsql - Custom fields Postgres 9.5

Hi, I've got this query, that I manually run it once a month: SELECT uuid, clientid), * FROM logging WHERE logtime BETWEEN '201611015' AND '201612015' As you can see, I select a date. So in December, the date will be: *BETWEEN '201612015' AND '201601015'*, for examp

Re: [GENERAL] PLPGSQL returning number of rows

On Thu, Nov 10, 2016 at 5:44 PM, Patrick B wrote: > Hi guys, > > I'm writing a simple Plpgsql function to delete some data from different > tables. > > The function starts with a select, and then 2 deletes after that. > > How can I return the number of rows that each delete performed? > > ​The pl

[GENERAL] PLPGSQL returning number of rows

Hi guys, I'm writing a simple Plpgsql function to delete some data from different tables. The function starts with a select, and then 2 deletes after that. How can I return the number of rows that each delete performed? CREATE or REPLACE FUNCTION delete_ids_clientid(account_id integer) RETURN

Re: [GENERAL] plpgsql update row from record variable

On 04/02/2016 06:07 PM, Seamus Abshere wrote: hi, I want to write a function that updates arbitrary columns and here's my pseudocode: CREATE OR REPLACE FUNCTION update_pet(id int, raw_data jsonb) RETURNS VOID AS $$ DECLARE data record; BEGIN SELECT jsonb_populate_record(null::pets, raw_da

[GENERAL] plpgsql update row from record variable

hi, I want to write a function that updates arbitrary columns and here's my pseudocode: CREATE OR REPLACE FUNCTION update_pet(id int, raw_data jsonb) RETURNS VOID AS $$ DECLARE data record; BEGIN SELECT jsonb_populate_record(null::pets, raw_data) INTO data; UPDATE pets [... from data ...] W

Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

Thank you all for your help. I currently only have the two cases to handle, so I went with the below if-else statement which works how I expected. -- Generate array of tables to create if (create_source) then the_tables := array[[new_table_schema, new_table_name],[new_table_schema||

Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

"David G. Johnston" writes: > ​or a more semantically meaning one...the use of the inner array is > arguably a hack here meant to avoid the overhead and new type creation by > assigning meaning to array slots. Yeah, good point: it looks like Mike does not consider the columns of the array to be

Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

On Mon, Dec 28, 2015 at 4:39 PM, Tom Lane wrote: > Michael Rasmussen writes: > > I am trying to iterate through a multidimensional array using a foreach > loop, as exampled in the documentation at > http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY >

Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

On Mon, Dec 28, 2015 at 4:05 PM, Michael Rasmussen wrote: > Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development. > > I am trying to iterate through a multidimensional array using a foreach > loop, as exampled in the documentation at > http://www.postgresql.org/docs/9.4/stati

Re: [GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

Michael Rasmussen writes: > I am trying to iterate through a multidimensional array using a foreach loop, > as exampled in the documentation at > http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY. > Here is a simplified version of the function: > C

[GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development. I am trying to iterate through a multidimensional array using a foreach loop, as exampled in the documentation at http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY. Here is a

Re: [GENERAL] plpgsql question: select into multiple variables ?

problem was in casts that I was using were confusing the parser and were un-necessary. Appreciate your thought and effort. Regards Dave From: Yasin Sari [mailto:yasinsar...@googlemail.com] Sent: Tuesday, June 30, 2015 3:26 AM To: Day, David Subject: Re: [GENERAL] plpgsql question: select into

Re: [GENERAL] plpgsql question: select into multiple variables ?

"David G. Johnston" writes: > On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: >> ... So what you wrote here is equivalent to >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO >> first_weekend FROM sys.calendar ... > ​Does it help to recognize the fact that "first_week

Re: [GENERAL] plpgsql question: select into multiple variables ?

On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: > Adrian Klaver writes: > > On 06/29/2015 12:07 PM, Day, David wrote: > >> What is wrong with my usage of the plpgsql "select into" concept > >> I have a function to look into a calendar table to find the first and > >> Last weekend date of a mon

Re: [GENERAL] plpgsql question: select into multiple variables ?

-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Monday, June 29, 2015 4:03 PM To: Day, David; pgsql-general@postgresql.org Subject: Re: [GENERAL] plpgsql question: select into multiple variables ? On 06/29/2015 12:07 PM, Day, David wrote: >

Re: [GENERAL] plpgsql question: select into multiple variables ?

Adrian Klaver writes: > On 06/29/2015 12:07 PM, Day, David wrote: >> What is wrong with my usage of the plpgsql "select into" concept >> I have a function to look into a calendar table to find the first and >> Last weekend date of a month. >> >> create or replace function sys.time_test () >> ret

Re: [GENERAL] plpgsql question: select into multiple variables ?

On 06/29/2015 12:07 PM, Day, David wrote: Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL

[GENERAL] plpgsql question: select into multiple variables ?

Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL for first or last weekend variable. cr

Re: [GENERAL] plpgsql functions organisation

On May 4, 2015 02:32:14 PM Yves Dorfsman wrote: > > As for performance concerns, in 99% of cases code maintainability is going > > to be way more important than performance microoptimization. If you're > > *that* concerned about performance than plpgsql probably isn't the right > > answer anyway. >

Re: [GENERAL] plpgsql functions organisation

> > As for performance concerns, in 99% of cases code maintainability is going to > be way more important than performance microoptimization. If you're *that* > concerned about performance than plpgsql probably isn't the right answer > anyway. Isn't one of the advantage of running on the server

Re: [GENERAL] plpgsql functions organisation

On 5/2/15 2:32 PM, Adrian Klaver wrote: On 05/02/2015 09:53 AM, Yves Dorfsman wrote: I find my plpgsql functions becomes unreadable very quickly. I want to break them up in smaller functions. What is the best way to organised them? Is there any way to define functions inside functions? When I

Re: [GENERAL] plpgsql functions organisation

On 05/03/2015 07:14 AM, Melvin Davidson wrote: The point was to show that yes, function calls take time, and using sub functions take even more time. I am not about to write an additional more detailed example just to show the same results. If you are in doubt, I respectfully suggest you do your

Re: [GENERAL] plpgsql functions organisation

The point was to show that yes, function calls take time, and using sub functions take even more time. I am not about to write an additional more detailed example just to show the same results. If you are in doubt, I respectfully suggest you do your own testing. On Sun, May 3, 2015 at 5:26 AM, Alb

Re: [GENERAL] plpgsql functions organisation

> On 03 May 2015, at 2:56, Melvin Davidson wrote: > > OK, Here is a simple example that shows the difference between using a self > contained function and > one that calls sub functions. > > After loading all the functions below, repeat each of the EXPLAIN statements > a few times and note

Re: [GENERAL] plpgsql functions organisation

OK, Here is a simple example that shows the difference between using a self contained function and one that calls sub functions. After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that callsubs takes almost TWICE as long to execute as nosub. CREATE

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 03:28 PM, Bill Moran wrote: On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver wrote: On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/02/2015 10:12 AM, Melvin Davidson wrote: AF

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 03:10 PM, Melvin Davidson wrote: Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load the

Re: [GENERAL] plpgsql functions organisation

On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver wrote: > On 05/02/2015 02:07 PM, Jeff Janes wrote: > > On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver > > wrote: > > > > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > > > > AFAIK, you cannot "package

Re: [GENERAL] plpgsql functions organisation

Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load the function from the catalogs, which involves overhe

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/02/2015 10:12 AM, Melvin Davidson wrote: AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a functio

Re: [GENERAL] plpgsql functions organisation

On Sat, 2 May 2015 14:07:31 -0700 Jeff Janes wrote: > On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver > wrote: > > > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > > > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible > >> to > >> call a function from within a function

Re: [GENERAL] plpgsql functions organisation

On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver wrote: > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible >> to >> call a function from within a function. >> >> That being said, I would seriously look at how and why you are

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 10:12 AM, Melvin Davidson wrote: AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not

Re: [GENERAL] plpgsql functions organisation

On 2015-05-02 11:12, Melvin Davidson wrote: > AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to > call a function from within a function. > > That being said, I would seriously look at how and why you are writing your > functions > as functions that call other functions a

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 09:53 AM, Yves Dorfsman wrote: I find my plpgsql functions becomes unreadable very quickly. I want to break them up in smaller functions. What is the best way to organised them? Is there any way to define functions inside functions? When I list functions in psql, I can see them al

Re: [GENERAL] plpgsql functions organisation

AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not very efficient. Also note that PostgreSQL allows

[GENERAL] plpgsql functions organisation

I find my plpgsql functions becomes unreadable very quickly. I want to break them up in smaller functions. What is the best way to organised them? Is there any way to define functions inside functions? When I list functions in psql, I can see them all at the same level, is there any way to organi

Re: [GENERAL] plpgsql code doen't work

Il 10/09/2013 10:46, Beena Emerson ha scritto: Hello, Try changing the variable left to something other like left_val. It will work. Maybe the problem is because LEFT is a keyword. Yes, left() is a function returning a 'text'. There's a conflict when you define it as an 'integer'... Giusepp

Re: [GENERAL] plpgsql code doen't work

Hello, Try changing the variable left to something other like left_val. It will work. Maybe the problem is because LEFT is a keyword. Beena Emerson

[GENERAL] plpgsql code doen't work

Hi,    I found following code: create or replace function plpgsql_edit_distance(stra text, strb text) returns integer as $$ declare rows integer; cols integer; begin rows := length(stra); cols := length(strb); IF rows = 0 THEN return cols; END IF; IF co

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

, 2013 6:14 PM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ? "Day, David" writes: > A complete self contained test case: example of the problem with my FOR LOOP > using a COMMON table expression. Ah, I see the problem.

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

"Day, David" writes: > A complete self contained test case: example of the problem with my FOR LOOP > using a COMMON table expression. Ah, I see the problem. It's got nothing particularly to do with CTEs; rather, your temporary variable is of the wrong rowtype: > drow test.tmm%ROWTYPE; -

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

On 08/09/2013 02:18 PM, Day, David wrote: A complete self contained test case: example of the problem with my FOR LOOP using a COMMON table expression. Again this is version 9.3beta Any comments Got it past the error by: Changing: drow test.tmm%ROWTYPE; -- deleted row holder to: drow rec

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

wsrow."pattern_match", wsrow."screen_class", wsrow."term_mode", wsrow."trans_result", wsrow."port_id", wsrow."tag_id", wsrow."cause__q850", wsrow."cause__redcom", wsrow.

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

: pgsql-general@postgresql.org Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ? "Day, David" writes: > Looking at the outermost for loop of the function below, If I run > this CTE query from the psql command line I am returned what I expect > for values for translato

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

"Day, David" writes: > Looking at the outermost for loop of the function below, If I run this CTE > query from the psql command line > I am returned what I expect for values for translator_id and the > Aggregating MIN functions. I restore the experimental data and now run the > function. > In

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

2013/8/9 Day, David : > Hi, > > > > I am working on a plpgsql function that is not acting as I would hope. > > I am working with the Postgres 9.3 beta load and would like to > > solicit some feedback. > > > > Looking at the outermost for loop of the function below, If I run this CTE > query from

[GENERAL] plpgsql FOR LOOP CTE problem ?

Hi, I am working on a plpgsql function that is not acting as I would hope. I am working with the Postgres 9.3 beta load and would like to solicit some feedback. Looking at the outermost for loop of the function below, If I run this CTE query from the psql command line I am returned what I exp

Re: [GENERAL] plpgsql plan caching allowing invalid data to enter table?

Joe Van Dyk writes: > It's looking like I can use a plpgsql function to insert data into a table > that violates a domain constraint. Is this a known problem? I think it's not really plpgsql's fault but domain_in's --- there's no provision for flushing the latter's cached info about how to check

Re: [GENERAL] plpgsql plan caching allowing invalid data to enter table?

On Tue, Jul 9, 2013 at 4:29 PM, Adrian Klaver wrote: > On 07/09/2013 04:05 PM, Joe Van Dyk wrote: > >> It's looking like I can use a plpgsql function to insert data into a >> table that violates a domain constraint. Is this a known problem? >> >> Session 1: >> >> create domain my_domain text check

Re: [GENERAL] plpgsql plan caching allowing invalid data to enter table?

On 07/09/2013 04:05 PM, Joe Van Dyk wrote: It's looking like I can use a plpgsql function to insert data into a table that violates a domain constraint. Is this a known problem? Session 1: create domain my_domain text check (length(value) > 2); create table my_table (name my_domain); create fu

Re: [GENERAL] plpgsql plan caching allowing invalid data to enter table?

As you can see, I have data in my_table that violates the check constraint. # select * from my_table; name ── test (1 row) # \d+ my_table Table "public.my_table" Column │ Type│ Modifiers │ Storage │ Stats target │ Description ┼───┼──

[GENERAL] plpgsql plan caching allowing invalid data to enter table?

It's looking like I can use a plpgsql function to insert data into a table that violates a domain constraint. Is this a known problem? Session 1: create domain my_domain text check (length(value) > 2); create table my_table (name my_domain); create function f(text) returns void as $$ declare my_

[GENERAL] plpgsql : looping over multidimensional array : getting NULL for subdimension

Use array_upper(aList, 2); see the example, maybe can help u CREATE OR REPLACE FUNCTION xxx( aList varchar[][]) returns TEXT as ' declare myUpper1 integer; myUpper2 integer; myRet varchar := ; begin myUpper1 := array_upper(aList, 1); IF myUpper1 IS NULL THEN

Re: [GENERAL] plpgsql cursor reuse

pgsql-general@postgresql.org" Sent: Monday, November 12, 2012 9:34 PM Subject: [GENERAL] plpgsql cursor reuse Came across this problem when trying to assign to a variable a field from a record that could come from multiple cursors. PG throws an error – “ ERROR:  type of parameter 7 (bigint

Re: [GENERAL] plpgsql cursor reuse

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Greco Sent: Monday, November 12, 2012 3:35 PM To: pgsql-general@postgresql.org Subject: [GENERAL] plpgsql cursor reuse Came across this problem when trying to assign to a variable a field

[GENERAL] plpgsql cursor reuse

Came across this problem when trying to assign to a variable a field from a record that could come from multiple cursors. PG throws an error - " ERROR: type of parameter 7 (bigint) does not match that when preparing the plan (unknown)". If I make the null column in c1 null::bigint to match curso

Re: [GENERAL] plpgsql: trigger insert new into other table (archive)

On 10/09/2012 09:55 AM, Matthijs Möhlmann wrote: Now we need to implement that all insert and update queries should be replicated to the some_archive database. Well, the question is how to do that? Whatever you do, please don't roll your own. This is a solved problem. If you plan on doing this

[GENERAL] plpgsql: trigger insert new into other table (archive)

Hello all, First the explanation: I have to databases, some_production and some_archive, those two databases have an identical layout. Now we need to implement that all insert and update queries should be replicated to the some_archive database. Well, the question is how to do that? I thought ab

Re: [GENERAL] Plpgsql 9.1.3 : not accepting "open", "close" as column names

hi, When using row."open" and row."close" the function was working fine. Many thanks for replying and help. Mark -- View this message in context: http://postgresql.1045698.n5.nabble.com/Plpgsql-9-1-3-not-accepting-open-close-as-column-names-tp5657223p5657967.html Sent from the PostgreSQL -

Re: [GENERAL] Plpgsql 9.1.3 : not accepting "open", "close" as column names

y, April 22, 2012 11:34 AM Subject: [GENERAL] Plpgsql 9.1.3 : not accepting "open", "close" as column names Hi , I have the following function which was working fine in Postgresql 8.4.7 CREATE OR REPLACE FUNCTION insert_stockpricemerge1(startdate character varying, enddate ch

Re: [GENERAL] Plpgsql 9.1.3 : not accepting "open", "close" as column names

On 04/22/2012 02:34 AM, fv967 wrote: Hi , I have the following function which was working fine in Postgresql 8.4.7 CREATE OR REPLACE FUNCTION insert_stockpricemerge1(startdate character varying, enddate character varying) RETURNS void AS $BODY$ DECLARE row RECORD; BEGIN FOR row

[GENERAL] Plpgsql 9.1.3 : not accepting "open", "close" as column names

Hi , I have the following function which was working fine in Postgresql 8.4.7 CREATE OR REPLACE FUNCTION insert_stockpricemerge1(startdate character varying, enddate character varying) RETURNS void AS $BODY$ DECLARE row RECORD; BEGIN FOR row IN SELECT stockid, date, open, high, low

Re: [GENERAL] plpgsql function to insert or update problem

On 03/24/2012 05:23 AM, Alban Hertroys wrote: On 23 Mar 2012, at 19:49, Andy Colson wrote: Anyway, the problem. I get a lot of DB Error messages: DB Error: ERROR: duplicate key value violates unique constraint "by_ip_pk" DETAIL: Key (ip, sessid, "time")=(97.64.237.59, 2qggi9gcdkcaoecqg3arvo1

Re: [GENERAL] plpgsql function to insert or update problem

On 23 Mar 2012, at 19:49, Andy Colson wrote: > Anyway, the problem. I get a lot of DB Error messages: > DB Error: ERROR: duplicate key value violates unique constraint "by_ip_pk" > DETAIL: Key (ip, sessid, "time")=(97.64.237.59, 2qggi9gcdkcaoecqg3arvo1gu7, > 2012-03-23 13:00:00) already exists

[GENERAL] plpgsql function to insert or update problem

Hi all, I am inserting apache log into into a database. Seem to have a little problem with this function: create or replace function insert_webstat( ivhost text, iip inet, isessid text, ihittime timestamp, iurl text, istatus integer, isi

Re: [GENERAL] plpgsql question

On Monday, December 05, 2011 6:59:32 am Gauthier, Dave wrote: > v8.3.4 on linux > > Is there a way to set the query used in a "for rec in (query) loop -> end > loop" be a variable? Example > > if (foo = 'whatever') > then > sqlstmt := "select x,y,z ..."; > else > sqlstmt := "select a,b,c ...

[GENERAL] plpgsql question

v8.3.4 on linux Is there a way to set the query used in a "for rec in (query) loop -> end loop" be a variable? Example if (foo = 'whatever') then sqlstmt := "select x,y,z ..."; else sqlstmt := "select a,b,c ..."; end if ; for therec in sqlstmt loop ... end loop; Thanks in Advance f

Re: [GENERAL] PLPGSQL: How can I get the effected rows when use "execute" command in function

if you are doing insert, update or delete you can use "retuirng" command in the query, work with a cursor to get the rows - Mensaje original - > Dear all, > >      Can I get the effected rows after executing sqls in function? > e.g.: > > CREATE OR REPLACE FUNCTION execsqls(sqls charac

Re: [GENERAL] PLPGSQL: How can I get the effected rows when use "execute" command in function

Hello yes, you can. Look on GET DIAGNOSTICS statement http://www.postgresql.org/docs/9.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Regards Pavel Stehule 2011/11/29 Muiz : > Dear all, > >    Can I get the effected rows after executing sqls in function? > e.g.: > > CREAT

[GENERAL] PLPGSQL: How can I get the effected rows when use "execute" command in function

Dear all, Can I get the effected rows after executing sqls in function? e.g.: CREATE OR REPLACE FUNCTION execsqls(sqls character varying) RETURNS integer AS $BODY$ DECLARE BEGIN EXECUTE sqls; -- TODO-1: I want to know how many records the input sqls effects?

Re: [GENERAL] plpgsql Difference in behaviour between versions?

Chris McDonald writes: > Still interested in a definitive answer, but the fix for me appears to be > simply to change the word open to something else. Well, if you want a definitive answer, you can consult the list of plpgsql reserved words here: http://git.postgresql.org/gitweb/?p=postgresql.gi

Re: [GENERAL] plpgsql Difference in behaviour between versions?

On Wednesday, November 23, 2011 11:43:04 am Chris McDonald wrote: > OK, I see it is the term open which fails the syntax checker - I guessed > this might be because open is a reserved word but > http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html > does not indicate whether op

Re: [GENERAL] plpgsql Difference in behaviour between versions?

OK, I see it is the term open which fails the syntax checker - I guessed this might be because open is a reserved word but http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html does not indicate whether open is either reserved or not in postgresql. Checking 8.4 doco, http:/

Re: [GENERAL] plpgsql Difference in behaviour between versions?

Jerry Sievers writes: > Hmmm, I do not see that open is a reserved word but the PL must be > treating it special somehow. plpgsql has a different list of reserved words than the main SQL grammar does. I don't think we explicitly document it anywhere, but pretty much any keyword that can start a

Re: [GENERAL] plpgsql Difference in behaviour between versions?

Chris McDonald writes: > Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to > postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed > that the following works on 8.4.8 but does not work on 9.0.5. Can > someone tell me why this is not legal syntax in 9.0.5 but is l

  1   2   3   4   5   >