pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pl/pgsql - code review + question
14+ messages / 6 participants
[nested] [flat]

* pl/pgsql - code review + question
@ 2001-07-18 14:10  Gary Stainburn <gary.stainburn@ringways.co.uk>
  0 siblings, 2 replies; 14+ messages in thread

From: Gary Stainburn @ 2001-07-18 14:10 UTC (permalink / raw)
  To: pgsql-sql

Hi all, I've just written my first pl/pgsql function (code included below for 
you to pull apart).

It takes an int4 mid (e.g. 15) and then using a select pulls out the team 
number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit 
number NE/012-02.

I now want to write the reverse function, where I can enter 'NE/012-02' and 
get back the mid 15.  The bit I'm stuck on is now I split the team part from 
the member part so that I can build the select statement.

TIA Gary

__BEGIN__
CREATE FUNCTION getunitno(int4) RETURNS varchar  AS '
DECLARE
     mid ALIAS FOR $1;
     results RECORD;
BEGIN
    select into results t.tnumber as tnumber, m.mnumber as mnumber
        from teams t, members m
        where t.tid = m.mteam and m.mid = mid;
    if results.mnumber < 10 then
      return results.tnumber || ''-0'' || results.mnumber;
    else
      return results.tnumber || ''-'' || results.mnumber;
    end if;
END;
' LANGUAGE 'plpgsql';
__END__

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 15:03  Richard Huxton <dev@archonet.com>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  1 sibling, 0 replies; 14+ messages in thread

From: Richard Huxton @ 2001-07-18 15:03 UTC (permalink / raw)
  To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql

From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>


> Hi all, I've just written my first pl/pgsql function (code included below
for
> you to pull apart).

Looks fine to me. Try it with "SELECT INTO" etc rather than "select into"
and see if you prefer it - I find it makes the variables/fields stand out
better.

> It takes an int4 mid (e.g. 15) and then using a select pulls out the team
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
> number NE/012-02.
>
> I now want to write the reverse function, where I can enter 'NE/012-02'
and
> get back the mid 15.  The bit I'm stuck on is now I split the team part
from
> the member part so that I can build the select statement.

Use the substr() function. Since you are careful to turn member-numbers into
2-digits you shouldn't need anything more complex.

richardh=> select substr('abcdefghijkl',2,3);
 substr
--------
 bcd
(1 row)

So, something like

teamnum := substr(idstring,1,6);
membnum := substr(idstring,7,2);

This can get you your team/member which you can query to get your "mid". If
you aren't enforcing uniqueness on (tnumber,mnumber) now might be a good
time to do so.

If the teamnum isn't always a fixed length search for the '-' with strpos()

richardh=> select strpos('abcdefg','e');
 strpos
--------
      5

HTH

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 15:24  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  1 sibling, 2 replies; 14+ messages in thread

From: Gary Stainburn @ 2001-07-18 15:24 UTC (permalink / raw)
  To: pgsql-sql

Okay, I've been hit round the back of the head, and I realised that the 
postgresql functions (inc subtring) are available in pl/pgsql, so that's my 
problem solved.

I've written the getmid function as below, which is basically the same as the 
getunitno I included in my first post.

My problem now is that when I include the code to handle the record not being 
there, from the pgsql chapter (section 23.2.3.3) I get the following errors 
based of the function below.  Can anyone explain why the concat of the string 
is failing. If I simply "raise exception ''member not found''" all works fine.

__BEGIN__ (screen output)
[revcom@curly revcom]$ psql -f t
DROP
CREATE
[revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
NOTICE:  plpgsql: ERROR during compile of getmid near line 15
ERROR:  parse error at or near "|"
[revcom@curly revcom]$
__END__
__BEGIN__ (~/t which contains the function def)
drop function getmid(varchar);
CREATE FUNCTION getmid(varchar) RETURNS int4  AS '
DECLARE
     unitno ALIAS FOR $1;
     teamno varchar;
     munit int4;
     results RECORD;
BEGIN
    teamno := substring(unitno from 1 for 6);
    munit := substring(unitno from 8);
    select into results m.mid as mid
        from teams t, members m
        where t.tid = m.mteam and 
              t.tnumber = ''teamno'' and
              m.mnumber = munit;
    if not found then
      raise exception ''Member '' || unitno || '' not found'';
      return 0;
    end if;
    return results.mid;
END;
' LANGUAGE 'plpgsql';
__END__

Gary
On Wednesday 18 July 2001  3:10 pm, Gary Stainburn wrote:
> Hi all, I've just written my first pl/pgsql function (code included below
> for you to pull apart).
>
> It takes an int4 mid (e.g. 15) and then using a select pulls out the team
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
> number NE/012-02.
>
> I now want to write the reverse function, where I can enter 'NE/012-02' and
> get back the mid 15.  The bit I'm stuck on is now I split the team part
> from the member part so that I can build the select statement.
>
> TIA Gary
>
> __BEGIN__
> CREATE FUNCTION getunitno(int4) RETURNS varchar  AS '
> DECLARE
>      mid ALIAS FOR $1;
>      results RECORD;
> BEGIN
>     select into results t.tnumber as tnumber, m.mnumber as mnumber
>         from teams t, members m
>         where t.tid = m.mteam and m.mid = mid;
>     if results.mnumber < 10 then
>       return results.tnumber || ''-0'' || results.mnumber;
>     else
>       return results.tnumber || ''-'' || results.mnumber;
>     end if;
> END;
> ' LANGUAGE 'plpgsql';
> __END__

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* RE: pl/pgsql - code review + question
@ 2001-07-18 15:46  Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 14+ messages in thread

From: Jeff Eckermann @ 2001-07-18 15:46 UTC (permalink / raw)
  To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; pgsql-sql

If the string will always be in that general form, use substring & position
functions (see "String Functions and Operators" in the docs.
Example: unit_number := substr(team_number, strpos(team_number, ''-'') + 1);
If you don't want the leading zero, you could make make the "+1" into "+2".
If you might have more than one leading zero, you could use ltrim:
unit_number := ltrim(unit_number, ''0'');

> -----Original Message-----
> From:	Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> Sent:	Wednesday, July 18, 2001 9:10 AM
> To:	pgsql-sql
> Subject:	pl/pgsql - code review + question
> 
> Hi all, I've just written my first pl/pgsql function (code included below
> for 
> you to pull apart).
> 
> It takes an int4 mid (e.g. 15) and then using a select pulls out the team 
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
> 
> number NE/012-02.
> 
> I now want to write the reverse function, where I can enter 'NE/012-02'
> and 
> get back the mid 15.  The bit I'm stuck on is now I split the team part
> from 
> the member part so that I can build the select statement.
> 
> TIA Gary
> 
> __BEGIN__
> CREATE FUNCTION getunitno(int4) RETURNS varchar  AS '
> DECLARE
>      mid ALIAS FOR $1;
>      results RECORD;
> BEGIN
>     select into results t.tnumber as tnumber, m.mnumber as mnumber
>         from teams t, members m
>         where t.tid = m.mteam and m.mid = mid;
>     if results.mnumber < 10 then
>       return results.tnumber || ''-0'' || results.mnumber;
>     else
>       return results.tnumber || ''-'' || results.mnumber;
>     end if;
> END;
> ' LANGUAGE 'plpgsql';
> __END__
> 
> -- 
> Gary Stainburn
>  
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* RE: pl/pgsql - code review + question
@ 2001-07-18 15:48  Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 1 reply; 14+ messages in thread

From: Jeff Eckermann @ 2001-07-18 15:48 UTC (permalink / raw)
  To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; pgsql-sql

I think you need to use syntax:
raise exception ''Member % Not Found'', unitno;

> -----Original Message-----
> From:	Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> Sent:	Wednesday, July 18, 2001 10:24 AM
> To:	pgsql-sql
> Subject:	Re: pl/pgsql - code review + question
> 
> Okay, I've been hit round the back of the head, and I realised that the 
> postgresql functions (inc subtring) are available in pl/pgsql, so that's
> my 
> problem solved.
> 
> I've written the getmid function as below, which is basically the same as
> the 
> getunitno I included in my first post.
> 
> My problem now is that when I include the code to handle the record not
> being 
> there, from the pgsql chapter (section 23.2.3.3) I get the following
> errors 
> based of the function below.  Can anyone explain why the concat of the
> string 
> is failing. If I simply "raise exception ''member not found''" all works
> fine.
> 
> __BEGIN__ (screen output)
> [revcom@curly revcom]$ psql -f t
> DROP
> CREATE
> [revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
> NOTICE:  plpgsql: ERROR during compile of getmid near line 15
> ERROR:  parse error at or near "|"
> [revcom@curly revcom]$
> __END__
> __BEGIN__ (~/t which contains the function def)
> drop function getmid(varchar);
> CREATE FUNCTION getmid(varchar) RETURNS int4  AS '
> DECLARE
>      unitno ALIAS FOR $1;
>      teamno varchar;
>      munit int4;
>      results RECORD;
> BEGIN
>     teamno := substring(unitno from 1 for 6);
>     munit := substring(unitno from 8);
>     select into results m.mid as mid
>         from teams t, members m
>         where t.tid = m.mteam and 
>               t.tnumber = ''teamno'' and
>               m.mnumber = munit;
>     if not found then
>       raise exception ''Member '' || unitno || '' not found'';
>       return 0;
>     end if;
>     return results.mid;
> END;
> ' LANGUAGE 'plpgsql';
> __END__
> 
> Gary
> On Wednesday 18 July 2001  3:10 pm, Gary Stainburn wrote:
> > Hi all, I've just written my first pl/pgsql function (code included
> below
> > for you to pull apart).
> >
> > It takes an int4 mid (e.g. 15) and then using a select pulls out the
> team
> > number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full
> unit
> > number NE/012-02.
> >
> > I now want to write the reverse function, where I can enter 'NE/012-02'
> and
> > get back the mid 15.  The bit I'm stuck on is now I split the team part
> > from the member part so that I can build the select statement.
> >
> > TIA Gary
> >
> > __BEGIN__
> > CREATE FUNCTION getunitno(int4) RETURNS varchar  AS '
> > DECLARE
> >      mid ALIAS FOR $1;
> >      results RECORD;
> > BEGIN
> >     select into results t.tnumber as tnumber, m.mnumber as mnumber
> >         from teams t, members m
> >         where t.tid = m.mteam and m.mid = mid;
> >     if results.mnumber < 10 then
> >       return results.tnumber || ''-0'' || results.mnumber;
> >     else
> >       return results.tnumber || ''-'' || results.mnumber;
> >     end if;
> > END;
> > ' LANGUAGE 'plpgsql';
> > __END__
> 
> -- 
> Gary Stainburn
>  
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 15:56  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 14+ messages in thread

From: Gary Stainburn @ 2001-07-18 15:56 UTC (permalink / raw)
  To: Jeff Eckermann <jeckermann@verio.net>; pgsql-sql

Hi Jeff,

That's sorted my exceptions out, now all I've got to do is find out why it's 
not finding the record in the first place.

Gary.

On Wednesday 18 July 2001  4:48 pm, Jeff Eckermann wrote:
> I think you need to use syntax:
> raise exception ''Member % Not Found'', unitno;
>
> > -----Original Message-----
> > From:	Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> > Sent:	Wednesday, July 18, 2001 10:24 AM
> > To:	pgsql-sql
> > Subject:	Re: pl/pgsql - code review + question
> >
> > Okay, I've been hit round the back of the head, and I realised that the
> > postgresql functions (inc subtring) are available in pl/pgsql, so that's
> > my
> > problem solved.
> >
> > I've written the getmid function as below, which is basically the same as
> > the
> > getunitno I included in my first post.
> >
> > My problem now is that when I include the code to handle the record not
> > being
> > there, from the pgsql chapter (section 23.2.3.3) I get the following
> > errors
> > based of the function below.  Can anyone explain why the concat of the
> > string
> > is failing. If I simply "raise exception ''member not found''" all works
> > fine.
> >
> > __BEGIN__ (screen output)
> > [revcom@curly revcom]$ psql -f t
> > DROP
> > CREATE
> > [revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
> > NOTICE:  plpgsql: ERROR during compile of getmid near line 15
> > ERROR:  parse error at or near "|"
> > [revcom@curly revcom]$
> > __END__
> > __BEGIN__ (~/t which contains the function def)
> > drop function getmid(varchar);
> > CREATE FUNCTION getmid(varchar) RETURNS int4  AS '
> > DECLARE
> >      unitno ALIAS FOR $1;
> >      teamno varchar;
> >      munit int4;
> >      results RECORD;
> > BEGIN
> >     teamno := substring(unitno from 1 for 6);
> >     munit := substring(unitno from 8);
> >     select into results m.mid as mid
> >         from teams t, members m
> >         where t.tid = m.mteam and
> >               t.tnumber = ''teamno'' and
> >               m.mnumber = munit;
> >     if not found then
> >       raise exception ''Member '' || unitno || '' not found'';
> >       return 0;
> >     end if;
> >     return results.mid;
> > END;
> > ' LANGUAGE 'plpgsql';
> > __END__
> >
> > Gary
> >
> > On Wednesday 18 July 2001  3:10 pm, Gary Stainburn wrote:
> > > Hi all, I've just written my first pl/pgsql function (code included
> >
> > below
> >
> > > for you to pull apart).
> > >
> > > It takes an int4 mid (e.g. 15) and then using a select pulls out the
> >
> > team
> >
> > > number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full
> >
> > unit
> >
> > > number NE/012-02.
> > >
> > > I now want to write the reverse function, where I can enter 'NE/012-02'
> >
> > and
> >
> > > get back the mid 15.  The bit I'm stuck on is now I split the team part
> > > from the member part so that I can build the select statement.
> > >
> > > TIA Gary
> > >
> > > __BEGIN__
> > > CREATE FUNCTION getunitno(int4) RETURNS varchar  AS '
> > > DECLARE
> > >      mid ALIAS FOR $1;
> > >      results RECORD;
> > > BEGIN
> > >     select into results t.tnumber as tnumber, m.mnumber as mnumber
> > >         from teams t, members m
> > >         where t.tid = m.mteam and m.mid = mid;
> > >     if results.mnumber < 10 then
> > >       return results.tnumber || ''-0'' || results.mnumber;
> > >     else
> > >       return results.tnumber || ''-'' || results.mnumber;
> > >     end if;
> > > END;
> > > ' LANGUAGE 'plpgsql';
> > > __END__
> >
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 16:24  Richard Huxton <dev@archonet.com>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  1 sibling, 1 reply; 14+ messages in thread

From: Richard Huxton @ 2001-07-18 16:24 UTC (permalink / raw)
  To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql

From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>

> My problem now is that when I include the code to handle the record not
being
> there, from the pgsql chapter (section 23.2.3.3) I get the following
errors

Hey - stop that! If everyone starts reading the docs and quoting chapter
refs all we'll be left with are *difficult* questions ;-)

> based of the function below.  Can anyone explain why the concat of the
string
> is failing. If I simply "raise exception ''member not found''" all works
fine.

Yep - this one keeps getting me too.

>       raise exception ''Member '' || unitno || '' not found'';

RAISE EXCEPTION ''Member % not found'', unitno;

Don't know why the parser for RAISE doesn't like string concat. Possibly
because it maps to the elog() error-reporting function underneath.

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 16:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
  1 sibling, 0 replies; 14+ messages in thread

From: Tom Lane @ 2001-07-18 16:29 UTC (permalink / raw)
  To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> My problem now is that when I include the code to handle the record
> not being there, from the pgsql chapter (section 23.2.3.3) I get the
> following errors based of the function below.

IIRC, there's an erroneous example in the 7.1 plpgsql documentation:
RAISE doesn't actually accept an expression for its string parameter,
only a literal (and only simple variables for the additional
parameters).  So you need to write something like

	raise exception ''Member % not found'', unitno;

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 20:34  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 2 replies; 14+ messages in thread

From: Tom Lane @ 2001-07-18 20:34 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql

"Richard Huxton" <dev@archonet.com> writes:
> Don't know why the parser for RAISE doesn't like string concat.

Laziness ;-).  Someone should fix plpgsql so that RAISE does take
expressions, not just literals and simple variables.  It probably
wouldn't be a big change, but I've no time to look at it myself;
any volunteers out there?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-18 23:53  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 14+ messages in thread

From: Josh Berkus @ 2001-07-18 23:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql

Tom, Folks,

> > Don't know why the parser for RAISE doesn't like string concat.
> 
> Laziness ;-).  Someone should fix plpgsql so that RAISE does take
> expressions, not just literals and simple variables.  It probably
> wouldn't be a big change, but I've no time to look at it myself;
> any volunteers out there?

While you're (whoever) at it, OFFSET won't take expressions either.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-19 09:04  Richard Huxton <dev@archonet.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 14+ messages in thread

From: Richard Huxton @ 2001-07-19 09:04 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql; Josh Berkus <josh@agliodbs.com>

From: "Tom Lane" <tgl@sss.pgh.pa.us>

> "Richard Huxton" <dev@archonet.com> writes:
> > Don't know why the parser for RAISE doesn't like string concat.
>
> Laziness ;-).  Someone should fix plpgsql so that RAISE does take
> expressions, not just literals and simple variables.  It probably
> wouldn't be a big change, but I've no time to look at it myself;
> any volunteers out there?

False Laziness perhaps (spot my Perl background)

OK - stick me down for having a look at it. Had a quick peek and I think
it's within my abilities. Give me a couple of weeks, because I haven't
looked at a YACC file since my university days (gram.y is YACC isn't it?)

I'll read up the rules for submitting patches and get something workable
back by August 3rd.

Josh - if I try and do OFFSET at the same time (presumably it's the same
change) do you fancy acting as a sanity test site?

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-19 10:09  Richard Huxton <dev@archonet.com>
  parent: Richard Huxton <dev@archonet.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Richard Huxton @ 2001-07-19 10:09 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; Josh Berkus <josh@agliodbs.com>

From: "Richard Huxton" <dev@archonet.com>

> False Laziness perhaps (spot my Perl background)
>
> OK - stick me down for having a look at it. Had a quick peek and I think
> it's within my abilities. Give me a couple of weeks, because I haven't
> looked at a YACC file since my university days (gram.y is YACC isn't it?)
>
> I'll read up the rules for submitting patches and get something workable
> back by August 3rd.
>
> Josh - if I try and do OFFSET at the same time (presumably it's the same
> change) do you fancy acting as a sanity test site?

What am I talking about - OFFSET is going to be parsed by the SQL parser not
the plpgsql parser.

Ignore me I'm blathering

- Richard Huxton





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-19 15:19  Josh Berkus <josh@agliodbs.com>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Josh Berkus @ 2001-07-19 15:19 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql

Richard,

> > Josh - if I try and do OFFSET at the same time (presumably it's the
> same
> > change) do you fancy acting as a sanity test site?
> 
> What am I talking about - OFFSET is going to be parsed by the SQL
> parser not
> the plpgsql parser.

Not so, not so!

Try the following two PL/pgSQL functions:

DECLARE
	h INT;
	k INT;
	a_row a%ROWTYPE;
BEGIN
	h := 1;
	k := 3;
	SELECT * FROM a INTO a_row
	ORDER BY a.1
	LIMIT 1 OFFSET (h + k);
END;

	... blows up, but  ...


DECLARE
	h INT;
	k INT;
	a_row a%ROWTYPE;
BEGIN
	h := 1;
	k := (3 + h);
	SELECT * FROM a INTO a_row
	ORDER BY a.1
	LIMIT 1 OFFSET k;
END;

 ... works.


As far as I can tell, PL/pgSQL is not evaluating the expression before
passing it on to the SQL parser.  Or is the SQL parser supposed to
accept (1 + 3) ....

Oh, I see what you mean.   Sorry!  Should I bug-traq this problem?  

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: pl/pgsql - code review + question
@ 2001-07-20 14:22  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Bruce Momjian @ 2001-07-20 14:22 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql; Josh Berkus <josh@agliodbs.com>

> Josh - if I try and do OFFSET at the same time (presumably it's the same
> change) do you fancy acting as a sanity test site?
> 

Both LIMIT and OFFSET seem to have that restriction.  I will add this to
the TODO list.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2001-07-20 14:22 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-18 14:10 pl/pgsql - code review + question Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-18 15:03 ` Richard Huxton <dev@archonet.com>
2001-07-18 15:24 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-18 16:24   ` Richard Huxton <dev@archonet.com>
2001-07-18 20:34     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-18 23:53       ` Josh Berkus <josh@agliodbs.com>
2001-07-19 09:04       ` Richard Huxton <dev@archonet.com>
2001-07-19 10:09         ` Richard Huxton <dev@archonet.com>
2001-07-19 15:19           ` Josh Berkus <josh@agliodbs.com>
2001-07-20 14:22         ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-07-18 16:29   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-18 15:46 RE: pl/pgsql - code review + question Jeff Eckermann <jeckermann@verio.net>
2001-07-18 15:48 RE: pl/pgsql - code review + question Jeff Eckermann <jeckermann@verio.net>
2001-07-18 15:56 ` Gary Stainburn <gary.stainburn@ringways.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox