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
>
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
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
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
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.
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.
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
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
On Tue, Jul 12, 2011 at 12:10 PM, Shianmiin wrote:
>
> Merlin Moncure-2 wrote:
>>
>> One proposed solution is to cache plpgsql plans around the search path.
>>
>
> I like the proposed solution, since search_path plays a part when generating
> plpgsql plan, it make sense to be part of the cache.
>
Merlin Moncure-2 wrote:
>
> One proposed solution is to cache plpgsql plans around the search path.
>
I like the proposed solution, since search_path plays a part when generating
plpgsql plan, it make sense to be part of the cache.
Merlin Moncure-2 wrote:
>
> *) use sql functions for porti
On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin wrote:
> We have recently gone thru an unexpected behavior of PostgreSQL function
> written in plpgsql.
> I wonder if anyone can help explain the ideas behind the design.
>
> Test scenario:
> 1. create two identical schemas, let's call them tenant1 and te
2011/6/9 Tom Lane :
> Merlin Moncure writes:
>> On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
>> wrote:
>>> (as far as I know) It's not possible for a function to see data committed by
>>> other transactions since that function began executing, whether or not those
>>> other transactions have comm
Merlin Moncure writes:
> On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
> wrote:
>> (as far as I know) It's not possible for a function to see data committed by
>> other transactions since that function began executing, whether or not those
>> other transactions have committed.
> This is not corre
On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer
wrote:
> On 9/06/2011 2:41 PM, Clemens Schwaighofer wrote:
>>
>> Hi,
>>
>> I have a plpgsql function where I read data from a table in a loop and
>> update data in a different table.
>>
>> Is it possible to see the updated data from a different access d
2011/6/9 Craig Ringer :
> On 9/06/2011 2:41 PM, Clemens Schwaighofer wrote:
>>
>> Hi,
>>
>> I have a plpgsql function where I read data from a table in a loop and
>> update data in a different table.
>>
>> Is it possible to see the updated data from a different access during
>> the run of this func
On 9/06/2011 2:41 PM, Clemens Schwaighofer wrote:
Hi,
I have a plpgsql function where I read data from a table in a loop and
update data in a different table.
Is it possible to see the updated data from a different access during
the run of this function? Or is this impossible because the functi
I can try this, but I have never done anything with plperl yet.
2011/6/9 pasman pasmański :
> If you rewrite your function in plperlu , you can store data in shared memory.
>
> 2011/6/9, Clemens Schwaighofer :
>> Hi,
>>
>> I have a plpgsql function where I read data from a table in a loop and
>> u
If you rewrite your function in plperlu , you can store data in shared memory.
2011/6/9, Clemens Schwaighofer :
> Hi,
>
> I have a plpgsql function where I read data from a table in a loop and
> update data in a different table.
>
> Is it possible to see the updated data from a different access du
> By making this function sql and immutable, you give the database more
> ability to inline it into queries which can make a tremendous
> performance difference in some cases. You can also index based on it
> which can be useful.
Very nice, Merlin. These aren't really a concern in my case as
I'
On Mon, Apr 4, 2011 at 7:12 PM, C. Bensend wrote:
>
> Hey folks,
>
> So, I'm working on a little application to help me with my
> budget. Yeah, there are apps out there to do it, but I'm having
> a good time learning some more too. :)
>
> I get paid every other Friday. I thought, for schedu
> generate_series(date '2001-01-05', date '2020-12-31', interval '2 weeks')
>
>
> will return every payday from jan 5 2001 to the end of 2020 (assuming
> the 5th was payday, change the start to jan 12 if that was instead).
And THERE is the winner. I feel like an idiot for not even
considering ge
> Not sure if your needs are like mine, but here is the function I use. It
> stores the date in a config table, and rolls it forward when needed. It
> also calculates it from some "know payroll date", which I'm guessing was
> near when I wrote it? (I'm not sure why I choose Nov 16 2008.) for m
> It is a very simplistic approach since you do not take into account
> holidays. But if it meets your needs what you want is the modulo operator
> (
> "%"; "mod(x,y)" is the equivalent function ) which performs division but
> returns only the remainder.
>
> N % 14 = [a number between 0 and (14 -
generate_series(date '2001-01-05', date '2020-12-31', interval '2 weeks')
will return every payday from jan 5 2001 to the end of 2020 (assuming
the 5th was payday, change the start to jan 12 if that was instead).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On 04/04/2011 07:12 PM, C. Bensend wrote:
Hey folks,
So, I'm working on a little application to help me with my
budget. Yeah, there are apps out there to do it, but I'm having
a good time learning some more too. :)
I get paid every other Friday. I thought, for scheduling
purposes in
It is a very simplistic approach since you do not take into account
holidays. But if it meets your needs what you want is the modulo operator (
"%"; "mod(x,y)" is the equivalent function ) which performs division but
returns only the remainder.
N % 14 = [a number between 0 and (14 - 1)]
N = 7;
On Fri, Sep 17, 2010 at 10:17 AM, bricklen wrote:
> On Fri, Sep 17, 2010 at 10:16 AM, Raymond O'Donnell wrote:
>> On 17/09/2010 18:12, bricklen wrote:
>>>
>>> On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell wrote:
That could be pretty useful - why don't you put it on the wiki?
On Fri, Sep 17, 2010 at 10:16 AM, Raymond O'Donnell wrote:
> On 17/09/2010 18:12, bricklen wrote:
>>
>> On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell wrote:
>>>
>>> That could be pretty useful - why don't you put it on the wiki?
>>>
>>> Ray.
>>>
>> I was going to put an entry at
>> http://wi
On 17/09/2010 18:12, bricklen wrote:
On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell wrote:
That could be pretty useful - why don't you put it on the wiki?
Ray.
I was going to put an entry at
http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I
couldn't find the "edit" option.
On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell wrote:
> That could be pretty useful - why don't you put it on the wiki?
>
> Ray.
>
I was going to put an entry at
http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I
couldn't find the "edit" option. Maybe I'm blind? I just noticed h
On 17/09/2010 17:37, bricklen wrote:
Here is a plpsql function I put together to search db functions in
schemas other than pg_catalog and information_schema. Not the greatest
of coding, but it might help someone else trying to solve the same
issue I was having: to search all public functions for
Jeff Ross writes:
> On 04/25/10 14:20, Tom Lane wrote:
>> Uh, you're using that as the destination for the FOR loop's SELECT.
>> What exactly is the purpose of having a second SELECT within the loop?
> How else do I get the results I want--name, address, city, state, and so
> on through the list
On 04/25/10 14:20, Tom Lane wrote:
Jeff Ross writes:
Now I'm *really* confused. I thought the table structure I created at
the beginning of the function was where the results would be returned
to.
Uh, you're using that as the destination for the FOR loop's SELECT.
What exactly is the purpose
Jeff Ross writes:
> Now I'm *really* confused. I thought the table structure I created at
> the beginning of the function was where the results would be returned
> to.
Uh, you're using that as the destination for the FOR loop's SELECT.
What exactly is the purpose of having a second SELECT with
On 25/04/2010 20:50, Jeff Ross wrote:
> Now I'm *really* confused. I thought the table structure I created at
> the beginning of the function was where the results would be returned
> to. I tried a variety of queries including select into and create table
> but they didn't work either.
I think
On 04/25/10 12:32, Tom Lane wrote:
Jeff Ross writes:
I'm trying to write my first plpgsql function and I'm running into a
problem that may or may not have to do with a coalesce statement.
No, it's not the coalesce ...
When I try to run this I get the following error:
jr...@acer:/var/www/
Jeff Ross writes:
> I'm trying to write my first plpgsql function and I'm running into a
> problem that may or may not have to do with a coalesce statement.
No, it's not the coalesce ...
> When I try to run this I get the following error:
> jr...@acer:/var/www/stars/sql $ psql -f view_all_trai
2009/8/17 David Fetter :
> On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote:
>> 2009/8/17 David Fetter :
>> > On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
>> >> Hello
>> >>
>> >> 2009/8/16 Andre Lopes :
>> >> > Hi,
>> >> >
>> >> > I need a plpgsql function to validade
On Sun, 2009-08-16 at 21:10 +0100, Andre Lopes wrote:
> I need a plpgsql function to validade e-mail addresses. I have google
> but I can't find any.
>
> My question: Anyone have a function to validate e-mails?
I recommend something based on the following recipe in PL/Perl.
http://wiki.postgres
On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote:
> 2009/8/17 David Fetter :
> > On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
> >> Hello
> >>
> >> 2009/8/16 Andre Lopes :
> >> > Hi,
> >> >
> >> > I need a plpgsql function to validade e-mail addresses. I have google
>
2009/8/17 David Fetter :
> On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
>> Hello
>>
>> 2009/8/16 Andre Lopes :
>> > Hi,
>> >
>> > I need a plpgsql function to validade e-mail addresses. I have google but I
>> > can't find any.
>> >
>> > My question: Anyone have a function to valid
On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
> Hello
>
> 2009/8/16 Andre Lopes :
> > Hi,
> >
> > I need a plpgsql function to validade e-mail addresses. I have google but I
> > can't find any.
> >
> > My question: Anyone have a function to validate e-mails?
> >
> > Best Regards,
Hello
2009/8/16 Andre Lopes :
> Hi,
>
> I need a plpgsql function to validade e-mail addresses. I have google but I
> can't find any.
>
> My question: Anyone have a function to validate e-mails?
>
> Best Regards,
> André.
>
You don't need plpgsql. Important is only an using of regular expression.
Tom Lane wrote:
Andre Lopes writes:
My question: Anyone have a function to validate e-mails?
Check the PG archives --- this has been discussed before. IIRC you
can't *really* validate them, short of actually sending mail.
And getting a reply.
But there are partial solutions in the archiv
Andre Lopes writes:
> My question: Anyone have a function to validate e-mails?
Check the PG archives --- this has been discussed before. IIRC you
can't *really* validate them, short of actually sending mail.
But there are partial solutions in the archives.
regards, tom l
On 16/08/2009 21:10, Andre Lopes wrote:
> I need a plpgsql function to validade e-mail addresses. I have google
> but I can't find any.
>
> My question: Anyone have a function to validate e-mails?
There are lots of regular expressions which Google will find for you,
which you can then use with on
Andreas Kendlinger <[EMAIL PROTECTED]> writes:
> I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...)
> from mySQL.
> ...
> One Method call requires 53ms.
Really? Near as I can tell, it takes about 130 microsec on my ancient
HPPA machine, which is surely as slow as anything a
=== PROBLEM SOLVED ===
I found for what I was looking
http://www.postgresql.org/docs/8.0/static/functions-array.html
Function : array_to_string (anyarray, text)
Return type : text
Description : concatenates array elements using provided delimiter
Example : array_to_string(array[1, 2, 3], '~^~')
R
Jiří Němec wrote:
> SET expiretime = expiretime + interval 'lifetime days'
> SET expiretime = expiretime + interval || lifetime || 'days'
SET expiretime = expiretime + lifetime * interval '1 day'
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
am Wed, dem 16.05.2007, um 15:11:17 +0200 mailte Ji?í N?mec folgendes:
> Hello,
>
> I have searched the Internet for an answer but nothing works for me.
>
> There is a plpgsql function which is used in a trigger. I need to
> substitute a value "7" for "lifetime" (integer) SELECTed in a previous
am 05.09.2005, um 14:26:31 -0300 mailte Sidnei de Souza folgendes:
> Is it possible to pass a table name and/or schema name to a plpgsql
> function?
Yes.
> How can I use them in the code? Which Types to use for each of the
> parameters?
varchar.
>
> E.g.
>
> create or replace function MyTe
David Lazar <[EMAIL PROTECTED]> writes:
> ERROR: RETURN cannot have a parameter in function returning set; use
> RETURN NEXT at or near "pkg" at character 1149
> Did something change from pgsql 7.X series set returning function to
> version 8.X series???
I think 8.0 actually complains that you
On Apr 25, 2005, at 4:07 PM, Ruff, Jeffry C. SR. wrote:
Thanks for the reply. After making the change I now get the following
ERROR: unterminated string
CONTEXT: compile of PL/pgSQL function "group_list" near line 6
Ok make that
string text := ;
I'm now spoiled with dollar quoting :)
John DeS
On Mon, Apr 25, 2005 at 02:40:29PM -0500, Ruff, Jeffry C. SR. wrote:
>
> CREATE FUNCTION userinfo.group_list(text) RETURNS text AS'
>DECLARE
> rec RECORD;
> string text := NULL;
>BEGIN
> FOR rec IN SELECT * FROM userinfo.userdb_groups WHERE username = $1 LOOP
>
On Apr 25, 2005, at 3:40 PM, Ruff, Jeffry C. SR. wrote:
I found this function on line [Thanks to Jeff Eckermann and Juerg
Rietmann] that takes the results of a query and creates a comma
delimited string. However when I run it I get no values. Any help
would be appreciated. I apologize if this i
You don't have to cast it as anything, just return a refcursor from your
function.
Say you return a refcursor called return_cursor
select myfunction(your_in_array);
fetch all from return_cursor;
If you are calling from a development environment, you put the return
value of the fuction (the refcu
"Relyea, Mike" <[EMAIL PROTECTED]> writes:
> Thanks for the input. This looks very promising. I have one further
> question. My SQL statement is going to pull data from more than one
> table in a relatively complex query. How do I cast the RETURNS portion
> of the function?
In current releases
)
INNER JOIN "tblBlockAC"
ON "Targets"."TargetID" = "tblBlockAC"."TargetID"
WHERE (("PrintSamples"."MachineID" = '2167' OR
"PrintSamples"."MachineID" = '2168' OR "PrintSample
you coud pass in criteria as a delimted string, then
pull out each arg something like this
CREATE or REPLACE FUNCTION test_func( varchar)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
IN_ARRAY text[] ;
arg1 varchar;
arg2 varchar;
arg3 varchar
begin
IN_ARRAY = string_to_array($1,'~^~');
arg1= IN_ARR
In article <[EMAIL PROTECTED]>,
"Relyea, Mike" <[EMAIL PROTECTED]> writes:
> I need to create my very first function. I'm using 8.0.2 and I need a
> function that I can call (from my client app) with an unknown number of
> criteria for a select query. The function will then return the results
>
-Original Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: Friday, February 04, 2005 4:49 PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 01:14:44PM -0600, Juan Casero (FL FLC) wrote:
> Here is the output
On Fri, Feb 04, 2005 at 01:14:44PM -0600, Juan Casero (FL FLC) wrote:
> Here is the output of that command. I ran it in a unix shell and
> redirected the psql output to a file so I haven't touched it...
Well, here's the problem. Your definition is:
> integer | public | trx_id | charact
PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 12:44:35PM -0600, Juan Casero (FL FLC) wrote:
> Sorry about that. I did forget one parameter...
>
> customer_service=# select
>
trx_id('JUANCASERO3055128218&
inal Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: Friday, February 04, 2005 1:56 PM
To: Juan Casero (FL FLC)
Cc: Postgresql General
Subject: Re: [GENERAL] plpgsql function errors
On Fri, Feb 04, 2005 at 12:44:35PM -0600, Juan Casero (FL FLC) wrote:
> Sorry about tha
On Fri, Feb 04, 2005 at 12:44:35PM -0600, Juan Casero (FL FLC) wrote:
> Sorry about that. I did forget one parameter...
>
> customer_service=# select
> trx_id('JUANCASERO3055128218',805,'CREDIT','02/02/05','1','1','Aventura'
> ,'02/01/05','Tom');
> ERROR: function trx_id("unknown", integer, "unk
On Fri, Feb 04, 2005 at 12:22:43PM -0600, Juan Casero (FL FLC) wrote:
> I tried putting those values into strings like you describe below but
> then the server bombs. e.g...
>
> customer_service=# select
> trx_id('JUANCASERO3055128218','CREDIT','02/02/05','1','1','Aventura','02
> /01/05','Tom');
wanted to
start with the simplest case possible to improve my chances of success
with the procedure and optimize it later. Any ideas on why the function
call fails?
Thanks,
Juan
-Original Message-----
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: Friday, February 04, 2005 1:06 PM
On Fri, Feb 04, 2005 at 11:40:50AM -0600, Juan Casero (FL FLC) wrote:
> Hi Everyone -
>
> I am new to this list and although I have been using postgresql on and
> off for about a year now. I am trying to develop a webapp using perl
> and cgi with postgresql 7.4.6 as a backend database. One of th
Hello Marcin,
Once, Tuesday, September 05, 2000, 8:42:59 PM, you wrote:
MM> Hi,
MM> creation of following function works just fine but usage not:):
MM> mtldb=# select mtldb_wykonane(0);
MM> ERROR: unexpected SELECT query in exec_stmt_execsql()
MM> Can You help me find an error?
MM> tia
MM> CRE
69 matches
Mail list logo
