[SQL] SPI_exec does not return!
Hi, if I do a SELECT on a non-existing table, SPI_exec won't return. I get, e. g. in pgaccess, an error message " does not exist", and my code below the function call is never reached. So why return error codes for SPI_exec when you cannot evaluate them? The background: I need to distinguish different client connections within a trigger function. To do this, I create a temporary table within a "login" function. My trigger must check for the existence of this function. I cannot think of any other method, since there is no "connection id" or something like that! This would also be an application for checking the existance of a temporary table, as was asked for in another thread in this list before. How can I check for the existence of a temporary table without getting interuppted? Thanks, Markus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] CAST(ipaddress as text) -- HOW?
hi, ipaddress::text is resulting in ERROR: Cannot cast type 'cidr' to 'text' how else we can??? Thankx. On Tue, 17 Jul 2001, omid omoomi wrote: > ie, > ipaddrss::text > > >From: Bhuvan A <[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED] > >Subject: [SQL] CAST(ipaddress as text) -- HOW? > >Date: Tue, 17 Jul 2001 19:35:53 +0530 (IST) > > > > > >hi all, > > > >how can we cast an ipaddress(type cidr) to any other type? > > > >thankx in advance.. > > > > > >Regards, > > > >Bhuvaneswar. > > > > > >---(end of broadcast)--- > >TIP 3: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to [EMAIL PROTECTED] so that your > >message can get through to the mailing list cleanly > > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SPI_exec does not return!
Markus Wagner wrote: > Hi, > > if I do a SELECT on a non-existing table, SPI_exec won't return. > I get, e. g. in pgaccess, an error message " does not exist", > and my code below the function call is never reached. Right, the SPI_exec() call in turn calls the PostgreSQL parser and that raises an elog(ERROR) which longjmp()'s back into the backends main loop waiting for the next client query. > > So why return error codes for SPI_exec when you cannot evaluate them? What do you want here? No error, just an empty result of unspecified format? If the parser cannot determine (due to nonexistance of a table) which datatype the columns of the result set at least should have, what else could he do than trowing an ERROR? > > The background: > > I need to distinguish different client connections within a trigger > function. > To do this, I create a temporary table within a "login" function. > My trigger must check for the existence of this function. > I cannot think of any other method, since there is no "connection id" or > something like that! Unfortunately PL/pgSQL doesn't have global variables. PL/Tcl can have. If you cannot code a part of the login mechanism in PL/Tcl, setting a global variable and returning that in another PL/Tcl function, you could create a temp table with the same name in every connection and fill the connection-ID into that. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] 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
Re: [SQL] 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Re: pl/pgsql - code review + question
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:[EMAIL PROTECTED]]
> > 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 [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] RE: pl/pgsql - code review + question
I think you need to use syntax:
raise exception ''Member % Not Found'', unitno;
> -Original Message-
> From: Gary Stainburn [SMTP:[EMAIL PROTECTED]]
> 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 [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] pl/pgsql - code review + question
From: "Gary Stainburn" <[EMAIL PROTECTED]>
> 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: pl/pgsql - code review + question
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:[EMAIL PROTECTED]] > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/pgsql - code review + question
From: "Gary Stainburn" <[EMAIL PROTECTED]> > 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pl/pgsql - code review + question
Gary Stainburn <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/pgsql - code review + question
"Richard Huxton" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How Postgresql Compares For Some Query Types
> > I'm a little unclear on what a "fact table" is. Can you explain? Good question... The idea comes from data warehousing where a typical data construction involves two "types" of table : dimensions : time, location, ethicity( essentially denormalized lookups ) facts : observations, accidents, sales facts have a composite primary key , each componant of which is a foreign key for one of the dimensions. If drawn with the fact table(s) in the center and dimensions around about, then the ERD looks like a "star". Hence the names "star schema" and "star query" A quick search located a vaguely helpful page : ( apologies about the source ) http://www.oradoc.com/ora816/server.816/a76994/schemas.htm A much better source is Kimball's book "The Data Warehousing Toolkit". regards Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] pl/pgsql - code review + question
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 [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] What is the syntax turn off auto commit?
On Wed, 18 Jul 2001, Raymond Chui wrote: > > > The Subject says its all. > > To speed up a bulk of INSERTs, I need to turn-off the auto commit 1st. > Then > at the end of INSERTs, issue COMMIT; > What is the syntax to turn off the auto commit? Thank you! Put them in an explicit transaction block begin insert ... insert ... commit; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
