Re: [GENERAL] Functions and Parentheses

On 10/6/2017 3:10 PM, Joshua D. Drake wrote: On 10/06/2017 02:33 PM, Tom Lane wrote: "Igal @ Lucee.org" writes: How come `current_date` has no parenthesis but `clock_timestamp()` does? Because the SQL standard says that CURRENT_DATE doesn't have parentheses. It is a function by any other m

Re: [GENERAL] Functions and Parentheses

On 10/06/2017 02:33 PM, Tom Lane wrote: "Igal @ Lucee.org" writes: How come `current_date` has no parenthesis but `clock_timestamp()` does? Because the SQL standard says that CURRENT_DATE doesn't have parentheses. It is a function by any other measure, though. (AFAICT, the SQL committee is u

Re: [GENERAL] Functions and Parentheses

"Igal @ Lucee.org" writes: > How come `current_date` has no parenthesis but `clock_timestamp()` does? Because the SQL standard says that CURRENT_DATE doesn't have parentheses. It is a function by any other measure, though. (AFAICT, the SQL committee is unacquainted with any principles of program

Re: [GENERAL] Functions and Parentheses

On Fri, Oct 6, 2017 at 2:18 PM, Igal @ Lucee.org wrote: > Hi, > > Is current_date a function? It's a bit puzzling to me since there are no > parentheses after it, i.e. > > SELECT current_date; > > And not > > SELECT current_date(); -- syntax error > ​ It, and the others like it, behave as f

[GENERAL] Functions and Parentheses

Hi, Is current_date a function?  It's a bit puzzling to me since there are no parentheses after it, i.e.   SELECT current_date; And not   SELECT current_date();  -- syntax error How come `current_date` has no parenthesis but `clock_timestamp()` does? Thanks, Igal Sapir Lucee Core Develope

Re: [GENERAL] functions - triggers cross schemas

Ok that was my oversight in understanding that the path does not change upon entering a function. I assumed that .it does by simply picking it from the schema2.test1 Thanks a bunch Armand On Oct 13, 2016, at 5:31 PM, David G. Johnston wrote: > On Thu, Oct 13, 2016 at 3:18 PM, Armand Pirvu

Re: [GENERAL] functions - triggers cross schemas

On Thu, Oct 13, 2016 at 3:18 PM, Armand Pirvu (home) wrote: > 2 schemas , schema1 and schema2, with same tables , a base table and a > tracking one. > > > Search path is set to schema1 by default. > insert into schema2.test1 (col2 , col3) values ('foo1','foo2') > I get an entry in schema1.test1_h

[GENERAL] functions - triggers cross schemas

2 schemas , schema1 and schema2, with same tables , a base table and a tracking one. Search path is set to schema1 by default. insert into schema2.test1 (col2 , col3) values ('foo1','foo2') I get an entry in schema1.test1_hist and not in schema2.test1_hist I understand that the trigger inhe

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

> > > I knew I was missing something:( > http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > "When an error is caught by an EXCEPTION clause, the local variables of the > PL/pgSQL function remain as they were when the error occurred, but all > c

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 10:49 AM, Tim Smith wrote: Did not see the strict. In any case I thought cleanSessionTable was cleaning out app_security.app_sessions not app_security.app_val_session_vw. Yes. cleanSessionTable does the actual cleaning. The point of the select from app_security.app_val_session_

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

> > Did not see the strict. In any case I thought cleanSessionTable was cleaning > out app_security.app_sessions not app_security.app_val_session_vw. Yes. cleanSessionTable does the actual cleaning. The point of the select from app_security.app_val_session_vw is that if the session is valid, the

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 10:02 AM, Tim Smith wrote: Me again, I've reworded it, but its still rolling back !!! Using the code below, if I call : select app_security.validateSession('XYZ','10.10.123.43','Z',5,5); I get an error raised on the select that follows cleanSessionTable. Which is fine. BUT, Postgr

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 09:54 AM, Tim Smith wrote: Well that was my problem, I did not know what was intended. apt-get install mind-reading ;-) 1) Look before you leap I'm confused by this option ? My script reads as follows : perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeou

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

Me again, I've reworded it, but its still rolling back !!! Using the code below, if I call : select app_security.validateSession('XYZ','10.10.123.43','Z',5,5); I get an error raised on the select that follows cleanSessionTable. Which is fine. BUT, Postgresql is still rolling back ! If I go back

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

> Well that was my problem, I did not know what was intended. apt-get install mind-reading ;-) > 1) Look before you leap > I'm confused by this option ? My script reads as follows : perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); select * into strict v_row from app_s

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 09:08 AM, Tim Smith wrote: Adrian, "what I want" is quite simple, I want the function to work as intended. ;-) Well that was my problem, I did not know what was intended. Let's step through the function : (1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTi

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

Adrian, "what I want" is quite simple, I want the function to work as intended. ;-) Let's step through the function : (1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); Function calls cleanSessionTable.cleanSessionTable is simple. It calls DELETE on the session

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 11:38 AM, Tim Smith wrote: > Adrian, > > Ok, let's start fresh. > > app_security.validateSession() calls app_security.cleanSessionTable(). > > app_security.cleanSessionTable(), when called on its, own, does not > cause me any issues. It operates as designed. > > I have ad

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 08:38 AM, Tim Smith wrote: Adrian, Ok, let's start fresh. app_security.validateSession() calls app_security.cleanSessionTable(). app_security.cleanSessionTable(), when called on its, own, does not cause me any issues. It operates as designed. I have added ROLLBACK TO SAVEPOINT

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

Tim Smith wrote on Friday, June 26, 2015 5:38 PM: > ERROR: cannot begin/end transactions in PL/pgSQL > HINT: Use a BEGIN block with an EXCEPTION clause instead. > CONTEXT: PL/pgSQL function > app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint) > line 16 at SQL statement >

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

Adrian, Ok, let's start fresh. app_security.validateSession() calls app_security.cleanSessionTable(). app_security.cleanSessionTable(), when called on its, own, does not cause me any issues. It operates as designed. I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now reads

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 07:24 AM, Tim Smith wrote: So this is in a plpgsql function? It is yes, but I thought I would spare you a copy/paste of the entire thing. The error trapping section currently looks like this : EXCEPTION WHEN OTHERS THEN RA

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 10:48 AM, Tim Smith wrote: > Hi David, > > I should have perhaps made clear this was a saved function, so my > understanding is ROLLBACK can't be used as its implicit. > ​I am pretty certain "ROLLBACK" cannot be used but the "ROLLBACK TO SAVEPOINT" can - they are and do t

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

Hi David, I should have perhaps made clear this was a saved function, so my understanding is ROLLBACK can't be used as its implicit. -- 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] Functions, savepoints, autocommit = I am confused !

> You need to trap exceptions and in the handler block issue a > > ROLLBACK TO SAVEPOINT > > http://www.postgresql.org/docs/9.4/static/sql-rollback-to.html > > > otherwise the the ROLLBACK issued at pg-session end will simply rollback > everything. > > David J. > Thanks, will take a look. -- Se

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

> So this is in a plpgsql function? It is yes, but I thought I would spare you a copy/paste of the entire thing. The error trapping section currently looks like this : EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Failed to validate session fo

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 06:38 AM, Tim Smith wrote: Hi, Apologies if I am being incredibly stupid, but I just can't seem to get this to work for me. I have a function that validates a web session is still active, so my code looks something like this : BEGIN perform app_security.cleanSessionTable(p_forced

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 9:38 AM, Tim Smith wrote: > I have a function that validates a web session is still active, so my > code looks something like this : > > BEGIN > perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); > SAVEPOINT sp_cleanedSessionTable; > select * into st

[GENERAL] Functions, savepoints, autocommit = I am confused !

Hi, Apologies if I am being incredibly stupid, but I just can't seem to get this to work for me. I have a function that validates a web session is still active, so my code looks something like this : BEGIN perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout); SAVEPOINT sp_cle

Re: [GENERAL] Functions

thank you it's helpfull On Fri, May 8, 2015 at 7:43 PM, Adrian Klaver wrote: > On 05/08/2015 01:56 AM, Ramesh T wrote: > >> Hi All, >>I want move functions from onedatabase to other database in same host >> on windows 7 and installed postgres version is 9.4.I'm using pgadmin3 >> tool. >> an

Re: [GENERAL] Functions

On 05/08/2015 01:56 AM, Ramesh T wrote: Hi All, I want move functions from onedatabase to other database in same host on windows 7 and installed postgres version is 9.4.I'm using pgadmin3 tool. any help..? Two options with pgAdmin: 1) See here http://www.pgadmin.org/docs/1.20/backup.html

[GENERAL] Functions

Hi All, I want move functions from onedatabase to other database in same host on windows 7 and installed postgres version is 9.4.I'm using pgadmin3 tool. any help..?

[GENERAL] Functions not visible in pg_stat_user_functions view

Hi all, I've notice not all my functions are tracked by pg_stat_user_functions view. Interesting thing is similar functions in different db are tracked correctly. query: SELECT p.* FROM pg_proc p LEFT JOIN pg_stat_user_functions stat ON (p.OID = stat.funcid) INNER JOIN pg_language l ON (l.oid =

[GENERAL] Functions that return a set in select column list

* Apologies if anyone receives this multiple times. I previously sent it with a subject that started with "Set" and it triggered some sort of admin filter. Can someone explain how set returning functions in a select clause work? It seems that it is doing some sort of implicit cross join. deli

Re: [GENERAL] Functions To Let Users Cancel/Terminate own Back Ends

On Feb 2, 2012, at 2:51 PM, Magnus Hagander wrote: >> I have a need at my $dayjob to let users cancel their own back ends. See any >> issues with this function to allow them to do that? Any security gotchas or >> anything? > > You mean something like this? > http://git.postgresql.org/gitweb/?p=

Re: [GENERAL] Functions To Let Users Cancel/Terminate own Back Ends

On Thu, Feb 2, 2012 at 23:38, David E. Wheeler wrote: > PostgreSQLers, > > I have a need at my $dayjob to let users cancel their own back ends. See any > issues with this function to allow them to do that? Any security gotchas or > anything? You mean something like this? http://git.postgresql.o

[GENERAL] Functions To Let Users Cancel/Terminate own Back Ends

PostgreSQLers, I have a need at my $dayjob to let users cancel their own back ends. See any issues with this function to allow them to do that? Any security gotchas or anything? CREATE OR REPLACE FUNCTION iov_cancel_user_backend( pid INTEGER ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFIN

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

Hello 2011/8/13 George MacKerron : >> On 12/08/2011 17:26, George MacKerron wrote: >>> >>> The point of the function is that you can pass it any table name >>> (along with some other parameters) and it returns rows from that >>> named table. >> >> OK, fair enough but what exactly are you tryin

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On 12 Aug 2011, at 19:17, Merlin Moncure wrote: >>> you can't have it both ways. at the time the function call is >>> executed, the return type/fields must be known. you can do this by >>> either a. explicitly defining the function return type or b. >>> describing the function return type in the

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

> On 12/08/2011 17:26, George MacKerron wrote: >> >> The point of the function is that you can pass it any table name >> (along with some other parameters) and it returns rows from that >> named table. > > OK, fair enough but what exactly are you trying to do that a simple > SELECT won't do?

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On Fri, Aug 12, 2011 at 12:01 PM, George MacKerron wrote: > On 12 Aug 2011, at 17:43, Merlin Moncure wrote: > >> you can't have it both ways. at the time the function call is >> executed, the return type/fields must be known.  you can do this by >> either a. explicitly defining the function return

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On 12 Aug 2011, at 17:43, Merlin Moncure wrote: > you can't have it both ways. at the time the function call is > executed, the return type/fields must be known. you can do this by > either a. explicitly defining the function return type or b. > describing the function return type in the function

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On 08/12/2011 10:04 AM, George MacKerron wrote: > Hi all. > > I have a function returning setof record. The name of a table it acts on is > one of its input variables, and its output is a set of rows from that table. > E.g. for simplicity, imagine it's this pointless function: > > create or repla

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On 12/08/2011 17:26, George MacKerron wrote: > Many thanks for the reply, Ray. > > Unfortunately, I don't think this addresses the problem, because I'd > hoped not to have to hard-code the table name into the function. > > The point of the function is that you can pass it any table name > (along

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On Fri, Aug 12, 2011 at 11:26 AM, George MacKerron wrote: > Many thanks for the reply, Ray. > > Unfortunately, I don't think this addresses the problem, because I'd hoped > not to have to hard-code the table name into the function. > > The point of the function is that you can pass it any table n

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

Many thanks for the reply, Ray. Unfortunately, I don't think this addresses the problem, because I'd hoped not to have to hard-code the table name into the function. The point of the function is that you can pass it any table name (along with some other parameters) and it returns rows from tha

Re: [GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

On 12/08/2011 17:04, George MacKerron wrote: > Hi all. > > I have a function returning setof record. The name of a table it acts > on is one of its input variables, and its output is a set of rows > from that table. E.g. for simplicity, imagine it's this pointless > function: > > create or replac

[GENERAL] Functions returning setof record -- can I use a table type as my return type hint?

Hi all. I have a function returning setof record. The name of a table it acts on is one of its input variables, and its output is a set of rows from that table. E.g. for simplicity, imagine it's this pointless function: create or replace function select_all_from(table_name text) returns setof

Re: [GENERAL] Functions as first-class values

On Wed, Apr 6, 2011 at 9:10 AM, Pavel Stehule wrote: > Hello > > 2011/4/6 Jon Smark : >> Hi, >> >> Is there support in PL/pgSQL for treating functions as first-class values? >> Consider the pseudo-code simple example below, which illustrates how this >> feature can be useful.  I reckon that this b

Re: [GENERAL] Functions as first-class values

Hello 2011/4/6 Jon Smark : > Hi, > > Is there support in PL/pgSQL for treating functions as first-class values? > Consider the pseudo-code simple example below, which illustrates how this > feature can be useful.  I reckon that this behaviour can be emulated using > EXECUTE, but I suspect there ar

[GENERAL] Functions as first-class values

Hi, Is there support in PL/pgSQL for treating functions as first-class values? Consider the pseudo-code simple example below, which illustrates how this feature can be useful. I reckon that this behaviour can be emulated using EXECUTE, but I suspect there are serious performance penalties. Your t

Re: [GENERAL] Functions - how to get the date created or updated

2010/1/14 Huda Booley (h...@careerjunction.co.za) : > Hi > > > > Is there a pg_stat table that one can query, or a script to use to determine > what date a function was created, or what date it was updated / modified? no regards Pavel Stehule > > > > Ta > > > > > > Huda Booley > DBA | CareerJunc

[GENERAL] Functions - how to get the date created or updated

Hi Is there a pg_stat table that one can query, or a script to use to determine what date a function was created, or what date it was updated / modified? Ta Huda Booley DBA | CareerJunction | Better jobs. More often. Web: www.careerjunction.co.za |Email: h...@careerjunction.co.za

Re: [GENERAL] Functions returning multiple rowsets

2009/9/28 Merlin Moncure > > > with s as (select * from two_sets()), > foo as (select unnest(_foos) from s), > bar as (select unnest(_bars) from s) > select > (select count(*) from foo) as no_foos, > (select count(*) from bar) as no_bars; > > merlin > I can see this working as we will be using

Re: [GENERAL] Functions returning multiple rowsets

On Sep 28, 2009, at 3:31 PM, Mike Christensen wrote: One thing I like about Microsoft SQL is you can write a sproc that does: SELECT * FROM TableA SELECT * FROM TableB And in .NET, you'll have a DataSet object with two DataTables, one for each table. Do either of the techniques outlined be

Re: [GENERAL] Functions returning multiple rowsets

2009/9/28 Mike Christensen : > One thing I like about Microsoft SQL is you can write a sproc that does: > > SELECT * FROM TableA > SELECT * FROM TableB > > And in .NET, you'll have a DataSet object with two DataTables, one for > each table.  Do either of the techniques outlined below provided this

Re: [GENERAL] Functions returning multiple rowsets

One thing I like about Microsoft SQL is you can write a sproc that does: SELECT * FROM TableA SELECT * FROM TableB And in .NET, you'll have a DataSet object with two DataTables, one for each table. Do either of the techniques outlined below provided this functionality, though I suppose in .NET y

Re: [GENERAL] Functions returning multiple rowsets

On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure wrote: > On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown wrote: >> Hi, >> >> Is it possible to create a function using 'SQL' as language which could >> return multiple rowsets, such as "SELECT * FROM TABLE1; SELECT * FROM >> TABLE2;" where both results

Re: [GENERAL] Functions returning multiple rowsets

On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown wrote: > Hi, > > Is it possible to create a function using 'SQL' as language which could > return multiple rowsets, such as "SELECT * FROM TABLE1; SELECT * FROM > TABLE2;" where both results are returned in the output?  I know this can be > done in stor

Re: [GENERAL] Functions returning multiple rowsets

Hello 2009/9/28 Thom Brown : > Hi, > > Is it possible to create a function using 'SQL' as language which could > return multiple rowsets, such as "SELECT * FROM TABLE1; SELECT * FROM > TABLE2;" where both results are returned in the output?  I know this can be > done in stored procedures in other

[GENERAL] Functions returning multiple rowsets

Hi, Is it possible to create a function using 'SQL' as language which could return multiple rowsets, such as "SELECT * FROM TABLE1; SELECT * FROM TABLE2;" where both results are returned in the output? I know this can be done in stored procedures in other RBDMS but can this be done in a function?

Re: [ADMIN] [GENERAL] Functions

Hello, If you want to get the value of a autogenerated column it's better to use "RETURNING" insert into something returning primary key... Best Regards, Rafael Domiciano Postgres DBA 2008/9/15 Scott Marlowe <[EMAIL PROTECTED]> > On Mon, Sep 15, 2008 at 11:53 AM, c k <[EMAIL PROTECTED]> > wrot

Re: [GENERAL] functions, selects and the planner

"Matthew Dennis" <[EMAIL PROTECTED]> writes: > My question is when is the "select into _data" query planned/replanned? I'm > concerned that the query might only be planned once (e.g. the first time > it's executed or when the function is first defined) and cached > indefinitely. The table t is in

[GENERAL] functions, selects and the planner

In PostgreSQL 8.3 lets say I have a table: create table t(c1 int, c2 int, ts0 timestamp with time zone, ts1 timestamp with time zone, data varchar); an index: create index t_c1_c2_ts0_idx on t using btree(c1, c2, ts0 desc); and a function: create function f(_ts timestamp(0) with time zone, _c1

Re: [GENERAL] functions, transactions, key violations

Apart from concurrency issues, it is possible that you have sequence generation problems. Depending on how you inserted the original rows into the 'purchases' table, it is possible that the nextval number has not kept-up and is lagging behind. You need to ensure that 'purchases_purchase_id_se

Re: [GENERAL] functions, transactions, key violations

On Wed, 2008-06-04 at 18:21 -0400, Michael Glaesemann wrote: > Yes, I saw the comment. I'm guessing I'm missing something wrt > transaction isolation level or locking. Would I need to use > SERIALIZABLE or some kind of locking? Is the function in the example > any different than the following

Re: [GENERAL] functions, transactions, key violations

Michael Glaesemann <[EMAIL PROTECTED]> writes: > However, my point regarding the example in the docs still holds. Why > is the exception block necessary? Doesn't wrapping the statements in a > function ensure the unique_violation couldn't occur? Well, the point of that example is to deal corre

Re: [GENERAL] functions, transactions, key violations

On Jun 4, 2008, at 6:00 PM, Jeff Davis wrote: Some other concurrent transaction could still insert something after the UPDATE but before the INSERT, so the unique constraint violation can still occur. Yes, I saw the comment. I'm guessing I'm missing something wrt transaction isolation leve

Re: [GENERAL] functions, transactions, key violations

> CREATE OR REPLACE FUNCTION > purchase(IN in_item_id integer, > IN in_purchased_by bigint, > IN in_purchase_price integer) > RETURNS VOID AS > $BODY$ > BEGIN > -- some selects > UPDATE purchases > SET purchase_status = 0 > WHERE item_id = in_item_id > AND

Re: [GENERAL] functions, transactions, key violations

On Jun 4, 2008, at 5:39 PM, Tom Lane wrote: I think you've anonymized the example into nonsense :-(. Now that I've provided yet another example of the perils of not providing the exact code, I've tried to patch it (below, for those of you willing to give me a second chance). However, my

Re: [GENERAL] functions, transactions, key violations

Michael Glaesemann <[EMAIL PROTECTED]> writes: > We have a function which has been throwing a key violation. We're > tracking the purchase history for a limited number of unique items, > and flagging the most recent purchase with purchase_status = 1. Um, the function is ensuring that there is

[GENERAL] functions, transactions, key violations

We have a function which has been throwing a key violation. We're tracking the purchase history for a limited number of unique items, and flagging the most recent purchase with purchase_status = 1. Stripped down, here's the schema and the function: CREATE TABLE purchases ( purchase_id SERI

Re: [GENERAL] Functions and transactions

On Tue, Apr 08, 2008 at 03:44:47PM +0200, Giorgio Valoti wrote: > Hi all, the documentation says that functions are always executed > within the transaction established by the outer query. However, I was > quite surprised (in a positive way, that is) seeing that if a > function call cau

[GENERAL] Functions and transactions

Hi all, the documentation says that functions are always executed within the transaction established by the outer query. However, I was quite surprised (in a positive way, that is) seeing that if a function call causes some error, the whole function is "rolled back". Is this the expected b

[GENERAL] functions, replication and portability was: Functional Index Question

On Fri, 14 Mar 2008 02:26:06 +0900 Craig Ringer <[EMAIL PROTECTED]> wrote: > To me it seems obvious that such routines should be in the > database, but I guess that's partly because of the sort of work I'm > doing. Other apps can then use the in-DB routines, rather than To me too but... > Then a

Re: [GENERAL] Functions refering directly to table columns?

How can I obtain this non-argument-requiring function feature? I solved it myself: Define the function using composite types (http://www.postgresql.org/docs/8.2/interactive/xfunc-sql.html) ---(end of broadcast)--- TIP 4: Have you searched our li

[GENERAL] Functions refering directly to table columns?

When using a function I need to parse each value as argument, e.g. myFunction(arg1, arg2, ..., arg999) However, if I can guarantee that the function will only be used with a certain table, can I then omit the arguments and letting the function refer to the table columns directly? In that way

Re: [GENERAL] Functions that return both Output Parameters and recordsets

Jeremy Nix <[EMAIL PROTECTED]> writes: > I see what you're doing, but I'm not quite sure how to adapt it to what > I'm doing. Here's simplified snippet of my code. Can elaborate on how > I can return a recordset and the output parameters.? I suppose what you need is something like CREATE OR R

Re: [GENERAL] Functions that return both Output Parameters and recordsets

On Mon, Jun 11, 2007 at 03:20:15PM +0200, Pavel Stehule wrote: > it's not possible. PostgreSQL doesn't support multiple recordset. You > have to have two functions. If you don't mind handling cursors then you could return multiple cursors from one function. See the PL/pgSQL documentation for an e

Re: [GENERAL] Functions that return both Output Parameters and recordsets

Pavel Stehule escribió: > Hello > > it's not possible. PostgreSQL doesn't support multiple recordset. You > have to have two functions. The other idea is to declare the function to return SETOF refcursor (or have an OUT refcursor param), and return two refcursors open with the different recordset

Re: [GENERAL] Functions that return both Output Parameters and recordsets

Hello, I forgot, You can do it via recordset of cursors. http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html 37.8.3.3. Returning Cursors one cursor returns TotalRecords and TotalPages columns and second record returns searchResult. Regards Pavel 2007/6/11, Jeremy Nix <[EMAIL PROT

Re: [GENERAL] Functions that return both Output Parameters and recordsets

Hello it's not possible. PostgreSQL doesn't support multiple recordset. You have to have two functions. Regards Pavel 2007/6/11, Jeremy Nix <[EMAIL PROTECTED]>: I see what you're doing, but I'm not quite sure how to adapt it to what I'm doing. Here's simplified snippet of my code. Can ela

Re: [GENERAL] Functions that return both Output Parameters and recordsets

I see what you're doing, but I'm not quite sure how to adapt it to what I'm doing. Here's simplified snippet of my code. Can elaborate on how I can return a recordset and the output parameters.? CREATE OR REPLACE FUNCTION Search (OUT TotalRecords int, OUT TotalPages int) RETURNS SETOF record

Re: [GENERAL] Functions that return both Output Parameters and recordsets

Hello it's possible, but it's probably some different than you expect CREATE OR REPLACE FUNCTION foo(OUT a integer, OUT b integer) RETURNS SETOF RECORD AS $$ BEGIN a := 10; b := 10; RETURN NEXT; a := 11; b := 20; RETURN NEXT; RETURN; END; $$ LANGUAGE plpgsql; postgres=# select * from foo(

[GENERAL] Functions that return both Output Parameters and recordsets

Is this possible? I'm attempting to create a function like this and I'm getting the following error: ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters at or near "myRecord". -- __ Jeremy Nix Senior Application Developer Southwest Fina

Re: [GENERAL] Functions, composite types and Notice

Thanks Tom. That did it. Theirs always something to learn, when i read you. Best regards Hakan > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Friday, February 02, 2007 4:04 PM > To: Hakan Kocaman > Cc: pgsql-general@postgresql.org > Sub

Re: [GENERAL] Functions, composite types and Notice

"Hakan Kocaman" <[EMAIL PROTECTED]> writes: >> Try this way instead: >> select * from public.check_notice(2,'hello'); > so i call the function now like this (obfuscated): > select > public.check_notice(t1.a,t1.b,t2.c) > from > public.tab1 t1, > public.tab2 t2 > I'm not clear h

Re: [GENERAL] Functions, composite types and Notice

> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 01, 2007 5:26 PM > To: Hakan Kocaman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Functions, composite types and Notice > > > "Hakan Kocaman" &

Re: [GENERAL] Functions, composite types and Notice

"Hakan Kocaman" <[EMAIL PROTECTED]> writes: > If you want to get the OUT-Params each as columns, you got to call it > this way: > =# select (public.check_notice(2,'hello')).*; Try this way instead: select * from public.check_notice(2,'hello'); regards, tom lane -

[GENERAL] Functions, composite types and Notice

Hi group, got a question regarding the different kinds calling a function returning record. This is 8.1.3 on gnu/linux. Consider this function: CREATE OR REPLACE FUNCTION public.check_notice( IN in_a int, IN in_b text, OUT out_a int, OUT out_b text ) RETURNS record as $BODY$ DECLARE BEGIN -- I

[GENERAL] Functions to obtain group members- PostgreSQL prior to 8.1

In PostgreSQL versions prior to 8.1, the functions to obtain group members is not present, so I wrote a couple of simple ones to illustrate how to do this. public.group_members(text) will return all members for a specific group. public.group_members() will return all members for all groups. F

Re: [GENERAL] Functions on tables

On Mon, Dec 18, 2006 at 12:15:34AM +1100, Brendan Jurd wrote: > On 12/17/06, Tom Lane <[EMAIL PROTECTED]> wrote: > >But having said all that, I think there are bits of SQL2003 that do some > >of what you're after. I don't think anyone has looked hard at what > >would be involved in merging those n

Re: [GENERAL] Functions on tables

On 12/17/06, Tom Lane <[EMAIL PROTECTED]> wrote: But having said all that, I think there are bits of SQL2003 that do some of what you're after. I don't think anyone has looked hard at what would be involved in merging those new SQL features with historical Postgres behaviors. I've been looking

Re: [GENERAL] Functions on tables

I wholeheartedly support the approach BJ is advocating. The notion that methods (functions) and variables (tables) can be defined together is a very useful OO approach. I too find it difficult to recall which functions "belong" to which tables. Of course, some of my functions are very generic and

Re: [GENERAL] Functions on tables

On 12/17/06, Tom Lane <[EMAIL PROTECTED]> wrote: 90% of the value this would have is already available with views, I think, without going outside bog-standard SQL: Views also work fine, but one of the big advantages of having table methods is that all the things your table can do are contained

Re: [GENERAL] Functions on tables

"Brendan Jurd" <[EMAIL PROTECTED]> writes: > That works fine, but wouldn't it be far more elegant if you could do > this instead: > CREATE TABLE person ( > id SERIAL PRIMARY KEY, > firstname TEXT NOT NULL, > lastname TEXT NOT NULL, > FUNCTION name() RETURNS text AS $$ SELECT firstname || ' ' |

[GENERAL] Functions on tables

In the object-relational context, the definition of a "relation" is much the same as the idea of a "class"; the columns in a table are analogous to the attributes of a class. The names of Postgres' system catalogs reflect this correlation (pg_class, pg_attribute). Likewise, each tuple within a r

Re: [GENERAL] functions and plans

On Tue, May 23, 2006 at 04:19:56PM +0200, Sim Zacks wrote: > I am trying to figure out some issues with my functions and I was hoping > someone could help me. > My understanding is that the plan is made when the function is run the > first time. That means that running analyze on a table after a

Re: [GENERAL] functions and plans

Truncate won't work for me because I don't completely empty the table, I just empty it for that user. Each user has the table populated with his data, basically to work like a temporary table. When the user runs a function it repopulates his working table and then until he runs the function agai

Re: [GENERAL] functions and plans

Sim Zacks <[EMAIL PROTECTED]> writes: > I have a table that I populate, select and delete the data each time the > function is run. Each time I run the function it takes longer to get the > data. In these tests, the data is the exact same every time. > I tried putting vacuum inside the function,

  1   2   >