Re: [SQL] PL/pgSQL function syntax question?
--- On Mon, 9/8/08, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > From: Ruben Gouveia <[EMAIL PROTECTED]> > Subject: [SQL] PL/pgSQL function syntax question? > To: "pgsql-sql" > Date: Monday, September 8, 2008, 9:40 PM > i get the following error when i try and create the > following function: > > Basically, i am trying to have two different dates compared > and only the > most recent returned to me. This seems pretty straight > forward, what I am > doing wrong here? > > create or replace function fcn_pick_date(v_dt date) > returns date as $$ > DECLARE > v_dt date; > BEGIN > for v_record in select last_periodic, last_boot > from mediaportal > loop > if v_dt >= v_record.last_boot then >v_dt := v_record.last_periodic; > else > v_dt := v_record.last_boot; > end if; > end loop; > return (v_dt); > END; > $$ LANGUAGE 'plpgsql'; > > > ERROR: loop variable of loop over rows must be record or > row variable at or > near "loop" at character 195 > > ** Error ** > > ERROR: loop variable of loop over rows must be record or > row variable at or > near "loop" > SQL state: 42601 > Character: 195 where you declare v_record? i think you have declare v_record to record or var array -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Function syntax ?
Does this syntax look correct? Can anyone think of a better way to write this? This function will accept two timestamp parameters and determine the highest of the two? create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp without time zone) returns timestamp without time zone as $$ DECLARE v_dt timestamp without time zone; v_dt2 timestamp without time zone; BEGIN v_dt := p_dt; v_dt2 := p_dt2; if v_dt >= v_dt2 then return v_dt; else return v_dt2; end if; END; $$ LANGUAGE 'plpgsql';
Re: [SQL] Function syntax ?
On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > Does this syntax look correct? Can anyone think of a better way to write > this? > > This function will accept two timestamp parameters and determine the highest > of the two? > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > p_dt2 timestamp without time zone) > returns timestamp without time zone as $$ > DECLARE > v_dt timestamp without time zone; > v_dt2 timestamp without time zone; > > BEGIN > v_dt := p_dt; > v_dt2 := p_dt2; > > if v_dt >= v_dt2 then > return v_dt; > else > return v_dt2; > end if; > > END; > $$ LANGUAGE 'plpgsql'; It certainly works, but there's no real need for the declarations. This works just as well: create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp without time zone) returns timestamp without time zone as $$ BEGIN if p_dt >= p_dt2 then return p_dt; else return p_dt2; end if; END; $$ LANGUAGE 'plpgsql'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function syntax ?
When i tried that, i got the following error: create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp without time zone) returns timestamp without time zone as $$ BEGIN v_dt := p_dt; v_dt2 := p_dt2; if v_dt >= v_dt2 then return v_dt; else return v_dt2; end if; END; $$ LANGUAGE 'plpgsql'; ERROR: syntax error at or near "v_dt" at character 1 QUERY: v_dt := $1 CONTEXT: SQL statement in PL/PgSQL function "fcn_max_dt" near line 3 ** Error ** ERROR: syntax error at or near "v_dt" SQL state: 42601 Context: SQL statement in PL/PgSQL function "fcn_max_dt" near line 3 On Tue, Sep 9, 2008 at 11:07 AM, Scott Marlowe <[EMAIL PROTECTED]>wrote: > On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <[EMAIL PROTECTED]> > wrote: > > Does this syntax look correct? Can anyone think of a better way to write > > this? > > > > This function will accept two timestamp parameters and determine the > highest > > of the two? > > > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > > p_dt2 timestamp without time zone) > > returns timestamp without time zone as $$ > > DECLARE > > v_dt timestamp without time zone; > > v_dt2 timestamp without time zone; > > > > BEGIN > > v_dt := p_dt; > > v_dt2 := p_dt2; > > > > if v_dt >= v_dt2 then > > return v_dt; > > else > > return v_dt2; > > end if; > > > > END; > > $$ LANGUAGE 'plpgsql'; > > It certainly works, but there's no real need for the declarations. > This works just as well: > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > p_dt2 timestamp without time zone) > returns timestamp without time zone as $$ > > BEGIN > if p_dt >= p_dt2 then >return p_dt; >else >return p_dt2; >end if; > > END; > $$ LANGUAGE 'plpgsql'; >
Re: [SQL] Function syntax ?
try create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp without time zone) returns imestamp without time zone as $$ select greatest($1,$2); $$ language sql; or begin return greatest(p_dt, p_dt2); end; $$ language plpgsql; or begin if p_dt > p_dt2 then return p_dt; else return p_dt2; end if; end; $$ language sql; plpgsql is scripting language and almost is better minimalize number of statements in function. Regards Pavel Stehule 2008/9/9 Ruben Gouveia <[EMAIL PROTECTED]>: > Does this syntax look correct? Can anyone think of a better way to write > this? > > This function will accept two timestamp parameters and determine the highest > of the two? > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > p_dt2 timestamp without time zone) > returns timestamp without time zone as $$ > DECLARE > v_dt timestamp without time zone; > v_dt2 timestamp without time zone; > > BEGIN > v_dt := p_dt; > v_dt2 := p_dt2; > > if v_dt >= v_dt2 then > return v_dt; > else > return v_dt2; > end if; > > END; > $$ LANGUAGE 'plpgsql'; > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function syntax ?
That's not what I copied and pasted in. Leave out the v_dt := p_dt; > v_dt2 := p_dt2; lines and turn the v into p in the rest of the function. On Tue, Sep 9, 2008 at 12:11 PM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > When i tried that, i got the following error: > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > p_dt2 timestamp without time zone) > returns timestamp without time zone as $$ > > BEGIN > v_dt := p_dt; > v_dt2 := p_dt2; > > if v_dt >= v_dt2 then > return v_dt; > else > return v_dt2; > end if; > > END; > $$ LANGUAGE 'plpgsql'; > > ERROR: syntax error at or near "v_dt" at character 1 > QUERY: v_dt := $1 > CONTEXT: SQL statement in PL/PgSQL function "fcn_max_dt" near line 3 > > ** Error ** > > ERROR: syntax error at or near "v_dt" > SQL state: 42601 > Context: SQL statement in PL/PgSQL function "fcn_max_dt" near line 3 > > > > > > > > > On Tue, Sep 9, 2008 at 11:07 AM, Scott Marlowe <[EMAIL PROTECTED]> > wrote: >> >> On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <[EMAIL PROTECTED]> >> wrote: >> > Does this syntax look correct? Can anyone think of a better way to write >> > this? >> > >> > This function will accept two timestamp parameters and determine the >> > highest >> > of the two? >> > >> > create or replace function fcn_max_dt(p_dt timestamp without time zone, >> > p_dt2 timestamp without time zone) >> > returns timestamp without time zone as $$ >> > DECLARE >> > v_dt timestamp without time zone; >> > v_dt2 timestamp without time zone; >> > >> > BEGIN >> > v_dt := p_dt; >> > v_dt2 := p_dt2; >> > >> > if v_dt >= v_dt2 then >> > return v_dt; >> > else >> > return v_dt2; >> > end if; >> > >> > END; >> > $$ LANGUAGE 'plpgsql'; >> >> It certainly works, but there's no real need for the declarations. >> This works just as well: >> >> create or replace function fcn_max_dt(p_dt timestamp without time zone, >> p_dt2 timestamp without time zone) >> returns timestamp without time zone as $$ >> >>BEGIN >>if p_dt >= p_dt2 then >>return p_dt; >>else >>return p_dt2; >>end if; >> >> END; >> $$ LANGUAGE 'plpgsql'; > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function syntax ?
Scott Marlowe wrote: > On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: >> Does this syntax look correct? Can anyone think of a better way to write >> this? >> >> This function will accept two timestamp parameters and determine the highest >> of the two? [snip] > It certainly works, but there's no real need for the declarations. > This works just as well: I'll see your improved function, and raise you some built-ins. => \df time*larger List of functions Schema |Name| Result data type | Argument data types ++-+-- pg_catalog | time_larger| time without time zone | time without time zone, time without time zone pg_catalog | timestamp_larger | timestamp without time zone | timestamp without time zone, timestamp without time zone pg_catalog | timestamptz_larger | timestamp with time zone| timestamp with time zone, timestamp with time zone pg_catalog | timetz_larger | time with time zone | time with time zone, time with time zone (4 rows) Also available for other built-in types. Been there ages, used by aggregate funcs iirc. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function syntax ?
thanks pavel...that worked! I like the simplicity of your first suggestion. On Tue, Sep 9, 2008 at 11:05 AM, Pavel Stehule <[EMAIL PROTECTED]>wrote: > try > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > p_dt2 timestamp without time zone) > returns imestamp without time zone as $$ > select greatest($1,$2); > $$ language sql; > > or > > begin > return greatest(p_dt, p_dt2); > end; > $$ language plpgsql; > > or > begin > if p_dt > p_dt2 then >return p_dt; > else >return p_dt2; > end if; > end; > $$ language sql; > > plpgsql is scripting language and almost is better minimalize number > of statements in function. > > Regards > Pavel Stehule > > 2008/9/9 Ruben Gouveia <[EMAIL PROTECTED]>: > > Does this syntax look correct? Can anyone think of a better way to write > > this? > > > > This function will accept two timestamp parameters and determine the > highest > > of the two? > > > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > > p_dt2 timestamp without time zone) > > returns timestamp without time zone as $$ > > DECLARE > > v_dt timestamp without time zone; > > v_dt2 timestamp without time zone; > > > > BEGIN > > v_dt := p_dt; > > v_dt2 := p_dt2; > > > > if v_dt >= v_dt2 then > > return v_dt; > > else > > return v_dt2; > > end if; > > > > END; > > $$ LANGUAGE 'plpgsql'; > > > > > > >
Re: [SQL] Function syntax ?
It appears there is already a greatest() and least() function available...so no need for creating this function. On Tue, Sep 9, 2008 at 11:16 AM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > thanks pavel...that worked! I like the simplicity of your first suggestion. > > > On Tue, Sep 9, 2008 at 11:05 AM, Pavel Stehule <[EMAIL PROTECTED]>wrote: > >> try >> >> create or replace function fcn_max_dt(p_dt timestamp without time zone, >> p_dt2 timestamp without time zone) >> returns imestamp without time zone as $$ >> select greatest($1,$2); >> $$ language sql; >> >> or >> >> begin >> return greatest(p_dt, p_dt2); >> end; >> $$ language plpgsql; >> >> or >> begin >> if p_dt > p_dt2 then >>return p_dt; >> else >>return p_dt2; >> end if; >> end; >> $$ language sql; >> >> plpgsql is scripting language and almost is better minimalize number >> of statements in function. >> >> Regards >> Pavel Stehule >> >> 2008/9/9 Ruben Gouveia <[EMAIL PROTECTED]>: >> > Does this syntax look correct? Can anyone think of a better way to write >> > this? >> > >> > This function will accept two timestamp parameters and determine the >> highest >> > of the two? >> > >> > create or replace function fcn_max_dt(p_dt timestamp without time zone, >> > p_dt2 timestamp without time zone) >> > returns timestamp without time zone as $$ >> > DECLARE >> > v_dt timestamp without time zone; >> > v_dt2 timestamp without time zone; >> > >> > BEGIN >> > v_dt := p_dt; >> > v_dt2 := p_dt2; >> > >> > if v_dt >= v_dt2 then >> > return v_dt; >> > else >> > return v_dt2; >> > end if; >> > >> > END; >> > $$ LANGUAGE 'plpgsql'; >> > >> > >> > >> > >
[SQL] How to provide password to pg_dump command ?
Good morning, Is there a way that I can pass "pwd" to pg_dump command please? I tried to pg_dump in java, but do not know how to pass password. Thanks a lot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to provide password to pg_dump command ?
Emi Lu написа: > Good morning, > > Is there a way that I can pass "pwd" to pg_dump command please? > > > I tried to pg_dump in java, but do not know how to pass password. Recommended method - http://www.postgresql.org/docs/current/static/libpq-pgpass.html Not recommended method - http://www.postgresql.org/docs/current/static/libpq-envars.html (look for PGPASSWORD). -- Milen A. Radev -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Function Syntax involving pipes and ' marks?
No matter how many times i try, i can't seem to get the write amount of ' marks around the date parameters in my v_where declaration. What am i doing wrong here? v_stmt should look like this if done correctly: select count(distinct m.id) from (select id, greatest(max(last_p),max(last_b)) as date_created from job group by id) m where m.jb_date < '2008-08-29' and m.jb >='2008-08-28'. when instead it's coming out like this: select count(distinct m.id) from (select id, greatest(max(last_periodic),max(last_boot)) as date_created from mediaportal group by id) m where m.date_created < 2008-08-29 and m.date_created >=2008-08-28 . ...no tick marks around the dates. here's my code: CREATE OR REPLACE FUNCTION fcn_job(p_date date, p_type varchar, p_jobid numeric) RETURNS numeric AS $$ DECLARE v_job numeric := 0; v_stmt varchar(1024); v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'|| ' and m.jb_date >='||p_date||''; BEGIN v_stmt := fcn_gen_statement(p_type, v_where, p_newonly); execute v_stmt into v_job; RAISE NOTICE 'sql looks like this: % . ',v_stmt; return v_job; END; $$ LANGUAGE plpgsql;
Re: [SQL] Function Syntax involving pipes and ' marks?
Ruben Gouveia escribió: > No matter how many times i try, i can't seem to get the write amount of ' > marks around the date parameters in my v_where declaration. What am i doing > wrong here? Apparently you're not aware that you can nest the $$ quote marks. You could just use $a$ to assign to the varchar, and use ' inside that string to get the literal '. Of course, you could use also $b$ instead of a plain single quote. That is, v_where varchar(256) := $a$ where m.jb_date < $a$||p_date + integer '1'|| $a$ and m.jb_date >='$a$ ||p_date|| $a$'$a$; or (harder to read) v_where varchar(256) := $a$ where m.jb_date < $a$||p_date + integer '1'|| $a$ and m.jb_date >=$b$ $a$ ||p_date|| $a$ $b$ $a$; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
