[GENERAL] PLPGSQL

Title: [GENERAL] PLPGSQL Hi All Im trying to assign the value of a dynamic variable, to a variable. How is this done in PostgresQL. Is this allowed in Postgres Procedural Language Here is my attempt. variable1:= quote_ident('variable_'||quote_literal(year)); Where the v

++ [GENERAL] PLPGSQL

Title: ++ [GENERAL] PLPGSQL Adding to that. Can you assign the value of a dynamic record value. For example If I have written dataset returned, into a record. I now want to call on a particular column value from the first row. The column name is dynamic. Can I use something like this

[GENERAL] PLPGSQL

Title: [GENERAL] PLPGSQL Is there no way in pgplsql to call on a dynamic column. I need to be able to dynamically determine the latest month column within a dataset, and to get that columns data. I am unfamiliar with other languages within Postgres Kind Regards, Shaun Clements

[GENERAL] plpgsql

We are trying to make some things work with plpgsql. The problem is that I built several functions that call one another, and I thought that the way of calling it was just making the assign: var:=func1(arg1,arg2); which gave me an error near ")". Now if I did the same, but like this: PERFORM

[GENERAL] plpgsql

hey all! I want to use a "LIKE" comparison in my plpgsql script, but I'm unsure how to do it... What I have right now doesn't work as hoped (although I did expect it to not work). UPDATE dir SET udate = curtime WHERE ''bind_dir.dir''::text LIKE ''%mydir%''::text AND dir.uid = bind_dir.u

[GENERAL] PLpgSQL debugger

Hello All, I have downloaded the PLpgSQl Debugger tarball edb-debugger-20061107-src.tar.gz after issuing gmake it gives me list of errors. But not getting how to install it. Can anybody tell me what are the steps to install it. Thanks in advance With Regards Ashish..

[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

Shaun Clements wrote: Can you assign the value of a dynamic record value. For example sales_month1_x := RECORDNAME.quote_ident('month1_'||quote_literal(yr2)); You're right - it won't work. PLPGSQL has strict type-checking, so it's no good for this sort of stuff. Perhaps pl/tcl/perl/python would be

Re: [GENERAL] PLPGSQL

On Mar 23, 2005, at 7:56 AM, Shaun Clements wrote: Is there no way in pgplsql to call on a dynamic column. I need to be able to dynamically determine the latest month column within a dataset, and to get that columns data. I am unfamiliar with other languages within Postgres Kind Regards, Shaun

FW: [GENERAL] PLPGSQL

Title: FW: [GENERAL] PLPGSQL Hi Sean Ive chosen the table structure on purpose. Im transforming data from one table to another. The problem is still there. I receive the column name from a query in one table, and then need to update the table with that column name in another. This needs

Re: [GENERAL] PLPGSQL

On Mar 23, 2005, at 8:51 AM, Shaun Clements wrote: Hi Sean Ive chosen the table structure on purpose. Im transforming data from one table to another. The problem is still there. I receive the column name from a query in one table, and then need to update the table with that column name in anoth

Re: [GENERAL] plpgsql

On Sat, Oct 18, 2003 at 06:48:10PM -0300, Martin Marques wrote: > We are trying to make some things work with plpgsql. The problem is that I > built several functions that call one another, and I thought that the way of > calling it was just making the assign: > > var:=func1(arg1,arg2); Have yo

[GENERAL] plpgsql question

I'm looking to find out how many rows were effected during an update in a trigger. I ran across this message by jan talking about this feature possibly being added to postgresql 6.5, but I can't find any reference to such a feature in the current documentation. Did this ever make it into pos

[GENERAL] plpgsql function

Hey guys I have written a plpgsql function which takes anyelement as argument. If I pass a string as input It gives an error "invalid input for integer" . Cant I pass any datatype for anyelement ---(end of broadcast)--- TIP 8: explain analyze is your

[GENERAL] plpgsql question

I'm trying to do a simple SELECT * in plpgsql that returns a set of records as a row w/ columns, not a row into a variable, w/ some conditionals. The function below is semi-pseudo with what I'm trying to... If anyone could give me an example that works by returning it as a resultset maintain

[GENERAL] plpgsql parameters

Hi, i have a recursive function building a xml file, from differents table. the xml is stored in memory and then flush on disk. the xml text is used in each recursive calls and i would like to know if in plpqsql parameters are passed by reference, or if there is a key word for passing parameter

[GENERAL] plpgsql function

Hello. I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...) from mySQL. //- CREATE OR REPLACE FUNCTION "BiSCAT_combined".extractyearmonth(date timestamp without time zone) RETURNS character varying AS $BODY$ DECLARE i INTEGER; BE

[GENERAL] plpgsql functions

Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS $BODY$begin select accgroups."accgroupid", COALESCE(sum(osc),0) as obc, COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as s

[GENERAL] plpgsql question

I am trying to calculate a value from a current record in a query and can't seem to get it working. Here is the shortened query; SELECT s.id, r.the_date_time, s.open_price, s.high_price, s.low_price, s.close_price, thesheet_onepair.symbol, r.buy_l

[GENERAL] PLPGSQL regexe

I'm doing a check within a PL/PGSQL function using a regular expression and I get the error: ERROR: regcomp failed with error invalid character range This appears to be because of the underscore "_" character. The regex I want to use is ''[A-Za-z0-9_]'' but this doesn't work. Any suggestions

[GENERAL] Plpgsql qestion

Hi All. Is it possible to create function (preferably in plpgsql) which gets table name and field name as an arguments and does select something from this table? Something like CREATE FUNCTION "ft" (text, text) RETURNS int4 AS ' declare res int4; begin select max($2

[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

[GENERAL] plpgsql and arrays

I'm trying to write function which accept array and insert each element in table: =# SELECT * FROM add_user('[EMAIL PROTECTED], [EMAIL PROTECTED]'); ERROR: column "email" is of type character varying but expression is of type record HINT: You will need to rewrite or cast th

[GENERAL] plpgsql and insert

Hi, Have a quick question is possible to record a primary from a insert stament eg xprimary := insert into schema.table(.,.,.,.) VALUES (); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] PLPGSQL SETOF functions

I am porting some Oracle code to PLPGSQL and am having a problem with functions that return SETOF datatype. In Oracle, the functions I'm porting return a TABLE of TYPE datatype, this TABLE being itself a named type. I am not aware of how to do this in PLPGSQL. Consider a function with header:

[GENERAL] plpgsql syntax error

Hi, I'm beginner in postgresql and plpgsql and i have the following problem. I'm trying to check a table if exists or not and when not exists then create it, but I get a syntax error. Here is my code: create or replace function check_table() returns void as $$ DECLARE mmonth integer; yyear in

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

[GENERAL] plpgsql function errors

Title: plpgsql function errors 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 the things I need is to create a transaction

Re: FW: [GENERAL] PLPGSQL

Shaun Clements wrote: Hi Sean Ive chosen the table structure on purpose. Im transforming data from one table to another. The problem is still there. I receive the column name from a query in one table, and then need to update the table with that column name in another. This needs to be done dynami

[GENERAL] plpgsql default arguments

Hello- I am running postgres on a Linux system and have been using plpgsql to write functions. Is there a way to specify default arguments? For example, I would like to offer the user to specify a date to use in my function but if no date is specified I would like to use the current date. Ea

[GENERAL] plpgsql and schema

The problem: I've two tables, both called "test" on two different schemas, let's say schema1 and schema2. I need to write a function like (just a simple example) CREATE OR REPLACE FUNCTION foo() RETURNS int4 AS $BODY$ declare num int4; begin select into num count(*) from test; return

[GENERAL] plpgsql function changes?

Hi, I run a function that returns a setof custom type rows against pgsql 7.4.6 and it works great. The same type and function was migrated to pgsql 8.0.3 but fails to run, returning an error: ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "pkg" at

[GENERAL] plpgsql FOUND Variable

Hi, when I run a SELECT INTO and the result is 0 or zero row, then I still get a positive result if using IF FOUND THEN Is there a problem or do we need to use ROW_COUNT instead ? Alex ---(end of broadcast)--- TIP 3: if posting/reading throu

[GENERAL] plpgsql , dynamic queries

Hi, I am having problems in forming a dynamic query that can be used in Execute statements. Problem: I have database fields that are either INT or VARCHAR. Both can have NULL. If I use variables that hold the db field's values ... how do I need to quote them to pass them to the query string. e

Re: [GENERAL] plpgsql question

DECLARE RowsAffected INTEGER; BEGIN -- DO your statement GET DIAGNOSTICS RowsAffected = ROW_COUNT; END On Tuesday 25 November 2003 02:56 pm, Brian Hirt wrote: > I'm looking to find out how many rows were effected during an update in > a trigger. I ran across this message by jan talking abou

Re: [GENERAL] PLpgSQL-Problem

Hello Tom, thank u for your fast reply! Now I understand that i can not split easily my scripts into logical units but what i not understand is the documention not correct or is my problem not similiar to the example in point 37.4.3 on http://www.postgresql.org/docs/7.4/static/plpgsql-declarati

Re: [GENERAL] PLpgSQL-Problem

"Thomas Chille" <[EMAIL PROTECTED]> writes: > Now I understand that i can not split easily my scripts into logical units but what > i not understand is the documention not correct > or is my problem not similiar to the example in point 37.4.3 on > http://www.postgresql.org/docs/7.4/static/plpgsql

[GENERAL] plpgsql/rule question

Is it possible to tell if a column in a NEW record in a plpgsql function was explicitly specified as NULL or simply left out altogether? For example, if I have the following table: create table foo(id serial, msg varchar) Is it possible to distinguish within plpgsql between these two q

Re: [GENERAL] plpgsql question

Assuming records is the name of a table... create or replace function getrecord(int,text) RETURNS SETOF records as $$ DECLARE row records%rowtype; BEGIN FOR row IN SELECT * FROM my_tbl WHERE ... LOOP RETURN NEXT row; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; On 1/5/06, Matthew Peter <[EMAIL

Re: [GENERAL] plpgsql question

On 1/5/06, Matthew Peter wrote: > I'm trying to do a simple SELECT * in plpgsql that returns a set of records > as a row w/ columns, not a row into a variable, w/ some conditionals. > > The function below is semi-pseudo with what I'm trying to... If anyone > could give me an example that wor

Re: [GENERAL] plpgsql question

On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: > Is it possible to skip the loop and just return all records in a > single query and shove all those rows into a table variable? Not in PL/pgSQL -- you need to return each row with RETURN NEXT, generally from within a loop. Why do yo

Re: [GENERAL] plpgsql question

Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:> Is it possible to skip the loop and just return all records in a> single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT,gene

Re: [GENERAL] plpgsql question

On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote: > Michael Fuhr <[EMAIL PROTECTED]> wrote: > > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: > > > Is it possible to skip the loop and just return all records in a > > > single query and shove all those rows into a table

Re: [GENERAL] plpgsql question

Michael Fuhr <[EMAIL PROTECTED]> wrote: On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:> Michael Fuhr wrote:> > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:> > > Is it possible to skip the loop and just return all records in a> > > single query and shove all those

Re: [GENERAL] plpgsql question

snip WHERE my_tbl_id = $1AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username ENDor WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)or WHERE my_tbl_id = $1 AND COALESCE($2, username) = usernameWith predicates such as these you wouldn't need to use EXECUTE andyou could write the query

Re: [GENERAL] plpgsql question

On Mon, Jan 09, 2006 at 01:01:33PM -0800, Matthew Peter wrote: > One other quick question, (figure it still applies to the subject > line :) when returning a row from a function I'm trying to include an > aggregate, but it's not showing up in the query result and I think > it's because it's not

Re: [GENERAL] plpgsql question

Terminology point: you used the word "aggregate" but the function below doesn't have an aggregate. Aggregates are functions thatoperate on multiple rows, like count() and sum(); substr() doesn'tdo that so it's not an aggregate. ya. my mistake.[snip] 1. Create a composite type with the

Re: [GENERAL] plpgsql parameters

FERREIRA, William (VALTECH) wrote: Hi, i have a recursive function building a xml file, from differents table. the xml is stored in memory and then flush on disk. the xml text is used in each recursive calls and i would like to know if in plpqsql parameters are passed by reference, I don't be

[GENERAL] Plpgsql Multidimensional array

Hi! I need to make a plpgsql function and I only think on a multidimensional array to make this function BUT I know that multidimensional arrays are not supported in plpgsql so, I wanna to receive some ideas from this list. Our system have a proprietary way to make his "ENCODING" , for exampl

[GENERAL] PLpgsql debugger question

Hi, Does anyone know if there is a debugger function that will return the line numbers that are executable? Also, is the debugger code available at pgfoundry the GUI client that EnterpriseDB has done or is the module that needs to be installed on the server? As I understand it the debugger

[GENERAL] plpgsql returning resultset

Hi I know the subject has been discussed before, but I dont find what any information that helps me make it work, so please bear with me. In pg 8.2 I want to write a function that gathers data from different tables and joins it into a single resultset, similar to "select * from tableA", but the p

Re: [GENERAL] plpgsql function

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

[GENERAL] plpgsql - or operator?

If I have an expression (a or b)a where a=TRUE and b=FALSE, why is b evaluated? Any true operand before an or operator means the entire expression is true . EXAMPLE create or replace function pinsusers() returns trigger as $$ declare msg varchar; begin

Re: [GENERAL] plpgsql functions

On 15/08/2008 20:12, c k wrote: Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS For functions return SETOF any type, you need to use the following idiom: ... declare

Re: [GENERAL] plpgsql functions

On Aug 15, 2008, at 1:47 PM, Raymond O'Donnell wrote: For functions return SETOF any type, you need to use the following idiom: Or, you can use, RETURN QUERY -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgr

Re: [GENERAL] plpgsql functions

2008/8/15 c k <[EMAIL PROTECTED]>: > Hi, > I am getting an error for a function written in plpgsql, as - > > CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) > RETURNS SETOF uf_closingbal AS > $BODY$begin > select accgroups."accgroupid", COALESCE(sum(osc),0) as obc, > COAL

[GENERAL] plpgsql Result Sets

When using a temp table in plpgsql functions that has columns comprised from many tables populated by joins, how do you specify a temp table return type when its generated by select into and dropped dynamically? I get an error when I specify returns setof temp_table. Also when I specify a perman

Re: [GENERAL] plpgsql question

Hello you used a wrong syntax see http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Usage_PL.2FpgSQL_function_with_parametres_of_type_table Regards Pavel Stehule 2010/8/28 Jon Griffin : >  I am trying to calculate a value from a current record in a query and can't > seem to get it working. > >

Re: [GENERAL] plpgsql function

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

Re: [GENERAL] plpgsql - cont'd

"Mitch Vincent" <[EMAIL PROTECTED]> writes: > Hmm, this is the third time this has happened.. I am using 7.1 Bert 3, so I > expected some things like this... When deleting and adding functions back, > when I run them I get : > ERROR: plpgsql: cache lookup for proc 49237 failed > -- What's happe

[GENERAL] plpgsql & documentation / tutor

Hi all, Is there any good tutor on plpgsql language ? With postgres docs there seems to be very little documentation on plpgsql. Thanks in advance. -- Rajesh Fowkar V. S. DEMPO & CO. LTD., PANAJI-GOA Email ID : [EMAIL PROTECTED], [EMAIL PROTECTED] Web Site : http://www.dempos.com ---

Re: [GENERAL] plpgsql/cursor

> can i declare and fetch from a cursor inside a plpgsql function? (pg version 7.0.3) In 7.1, the pl/pgsql document says you can code like this: DECLARE rec record; BEGIN for rec in SELECT * from MyTable LOOP update tableX set tableX=rec.fieldA; END LOOP; END; CN --

Re: [GENERAL] plpgsql/cursor

augie wrote: > can i declare and fetch from a cursor inside a plpgsql function? (pg > version 7.0.3) > > if so, what would the syntax look like? i continue to get parse errors on > 'cursor' in a very, very simple test. Refernce cursors will occur in PostgreSQL v7.2. Not sure when it'll

Re: [GENERAL] plpgsql question...

Steve Wampler <[EMAIL PROTECTED]> writes: > PostgreSQL 6.5.3 > > appdb=> create function insert_or_update() returns opaque as ' > appdb'> begin > appdb'> insert into attributes_table values(new.id,new.name, > appdb'>new.units,new.value); > appdb'> return NULL; > appdb

[GENERAL] plpgsql test function

This simple test function fails. -- drop function testit(text); create function testit(text) returns text as ' DECLARE myval text; BEGIN myval:= $1; return myval; END; ' language 'plpgsql'; select testit('testing'); select testit(''testing'

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

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

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

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

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

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

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

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

Artis Caune wrote: I'm trying to write function which accept array and insert each element in table: =# SELECT * FROM add_user('[EMAIL PROTECTED], [EMAIL PROTECTED]'); ERROR: column "email" is of type character varying but expression is of type record HINT: You will need t

Re: [GENERAL] plpgsql and arrays

Thanks a lot! Normal upper/lower loop works fine: CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT AS $$ DECLARE v_uid SMALLINT; v_low SMALLINT; v_upp SMALLINT; empty BOOLEAN := TRUE; BEGIN SELECT INTO v_low array_lower($1,1); SELECT INTO v_upp array_upper($1,1)

Re: [GENERAL] plpgsql and arrays

Artis Caune wrote: Thanks a lot! Normal upper/lower loop works fine: CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT I'd rename the function "add_users()" AS $$ DECLARE v_uid SMALLINT; v_low SMALLINT; v_upp SMALLINT; empty BOOLEAN := TRUE; BEGIN SELECT INTO

Re: [GENERAL] plpgsql and insert

Please say that again in English? On 3/5/07, Jamie Deppeler <[EMAIL PROTECTED]> wrote: Hi, Have a quick question is possible to record a primary from a insert stament eg xprimary := insert into schema.table(.,.,.,.) VALUES (); ---(end of broadcast)--

Re: [GENERAL] plpgsql and insert

Depending on what client side library you are using you could use the RETURNING clause, see the docs: http://www.postgresql.org/docs/8.2/static/sql-insert.html Regards, Ben "Jamie Deppeler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > Have a quick question is possible

[GENERAL] plpgsql using EXECUTE function

Hi, I would like to have one function in plpgsql which is gen_func. From this function I would like to call other functions depending on the input parameters of gen_func - lets say f1 and f2. Parameters to f1 or f2 are text, refcursor and text[]. I tried to use EXECUTE but the problem is how to

Re: [GENERAL] PLPGSQL SETOF functions

1) If you declare a return type setof TABLENAME the resultset will contain rows with field definitions like the table. 2) To call the function from another plpgsql function use: declare row record begin for row in select * from dates_pkg.getbusinessdays(...) Loop ...process...

Re: [GENERAL] PLPGSQL SETOF functions

Please reply to the list in the future. I don't believe you can do that. Sim On 06/29/2011 04:39 PM, David Greco wrote: Thanks that works pretty well. Is it possible to fetch the all the return of dates_pkg.getbusinessdays() into a single variable at once? i.e. in Oracle I would do somethin

[GENERAL] plpgsql function confusing behaviour

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 tenant2 -- set up tenant1 create schema tenant1; set search_pat

Re: [GENERAL] plpgsql syntax error

Hi, On 10 October 2011 21:35, József Kurucz wrote: > ERROR:  syntax error at or near "$1" > LINE 1: create table  $1  ( ) >                      ^ > QUERY:  create table  $1  ( ) > CONTEXT:  SQL statement in PL/PgSQL function "check_table" near line 22 I think you have to use "execute": execute

Re: [GENERAL] plpgsql syntax error

On Monday, October 10, 2011 3:35:27 am József Kurucz wrote: > Hi, > > I'm beginner in postgresql and plpgsql and i have the following problem. > I'm trying to check a table if exists or not and when not exists then > create it, but I get a syntax error. > > Here is my code: > > > create or repl

Re: [GENERAL] plpgsql syntax error

Many Thanks!!! It works! Regards, Josef 2011/10/10 Ondrej Ivanič : > Hi, > > On 10 October 2011 21:35, József Kurucz wrote: >> ERROR:  syntax error at or near "$1" >> LINE 1: create table  $1  ( ) >>                      ^ >> QUERY:  create table  $1  ( ) >> CONTEXT:  SQL statement in PL/PgSQL

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

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

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

  1   2   3   4   5   >