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

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

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

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

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

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 >

[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

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

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

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

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 >

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 >

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 >: On Wed, Dec 14, 2016 at 5:12 PM, rob stone >wrote: On Wed, 2016-12-14 at

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

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

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

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

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

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

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

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

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

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

[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

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

[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)

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,

[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 ...]

[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

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

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 >

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

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 >

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,

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

[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.

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: Hi

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

On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Adrian Klaver adrian.kla...@aklaver.com 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

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

Adrian Klaver adrian.kla...@aklaver.com 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

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

David G. Johnston david.g.johns...@gmail.com writes: On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane t...@sss.pgh.pa.us 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

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

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. Isn't

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

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 to

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,

Re: [GENERAL] plpgsql functions organisation

On 03 May 2015, at 2:56, Melvin Davidson melvin6...@gmail.com 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

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

[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

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 are not

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

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

Re: [GENERAL] plpgsql functions organisation

On Sat, 2 May 2015 14:07:31 -0700 Jeff Janes jeff.ja...@gmail.com wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver 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

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 Sat, May 2, 2015 at 1:05 PM, Adrian Klaver 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 function from within a function. That being said, I would seriously look at how and

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 adrian.kla...@aklaver.com 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

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

Re: [GENERAL] plpgsql functions organisation

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

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 05/02/2015 03:28 PM, Bill Moran wrote: On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver adrian.kla...@aklaver.com wrote: On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On

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

[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

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

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'...

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 d...@redcom.com 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

[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

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

2013/8/9 Day, David d...@redcom.com: 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

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

Day, David d...@redcom.com 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

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ? Day, David d...@redcom.com 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

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

@postgresql.org Subject: Re: [GENERAL] plpgsql FOR LOOP CTE problem ? Day, David d...@redcom.com 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

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

Re: [GENERAL] plpgsql FOR LOOP CTE problem ?

Day, David d...@redcom.com 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

[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

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

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

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

On Tue, Jul 9, 2013 at 4:29 PM, Adrian Klaver adrian.kla...@gmail.comwrote: 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

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

Joe Van Dyk j...@tanga.com 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

[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

[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 cursor

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

Re: [GENERAL] plpgsql cursor reuse

-hanks.com To: pgsql-general@postgresql.org 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

[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

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

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

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

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

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 character varying)   RETURNS void

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

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,

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

[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 for any

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 ...; end if ;

[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: 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 work.m...@gmail.com: Dear all,    Can I get the effected rows after executing sqls in function?

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 character

[GENERAL] plpgsql Difference in behaviour between versions?

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 legal in 8.4.8 please:

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

Hello http://developer.postgresql.org/pgdocs/postgres/release-9-0.html PL/pgSQL no longer allows variable names that match certain reserved words (Tom Lane) use double quotes rec.open = 32; Regards Pavel Stehule 2011/11/23 Chris McDonald chrisjonmcdon...@gmail.com: Hi, I am upgrading a

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

Chris McDonald chrisjonmcdon...@gmail.com 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

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

Chris McDonald chrisjonmcdon...@gmail.com 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

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

Jerry Sievers gsiever...@comcast.net 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

  1   2   3   4   5   >