[SQL] Re: PL/PGSQL function with parameters
Unfortunately you cannot use parameters as you like. The FROM clause cannot contain a parameter. It must be constant. Zoltan -- Kov\'acs, Zolt\'an [EMAIL PROTECTED] http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
Re: [SQL] Problem with Day of Week
Ok, so there is actually two standards then. Is this documented
anywhere? Is this is something that is going to change? I don't want
to write and app and have things "break" during and upgrade :)
Thanks for the response.
On Mon, 5 Feb 2001, Karel Zak wrote:
>
> On Mon, 29 Jan 2001, Keith Perry wrote:
>
> > Greetings,
> >
> > I notice some talk about date problems and interestingly enough planning
> > out an application in which I will need to be able to manipulate dates.
> > I notice however that there seems to be a discrepancy with the day or
> > week in 7.0.3
> >
> > ---
> >
> > pmhcc=# select date_part('dow','now'::timestamp);
> > date_part
> > ---
> > 1
> > (1 row)
> >
> > pmhcc=# select to_char('now'::timestamp,'D');
> > to_char
> > -
> > 2
> > (1 row)
> >
>
> See:
>
> test=# select date_part('dow','2001-02-11'::timestamp);
> date_part
> ---
> 0
>
> test=# select to_char('2001-02-11'::timestamp, 'D');
> to_char
> -
> 1
>
>
> date_part is based on zero - use range 0-6
> to_char is based on one - use range 1-7
>
> Karel
>
[SQL] 1024 limit???
Hi, Im building a server that uses libpq to connect to a database and authenticate the users that connect. I do PQfinish for the conn and PQclear for the result so there cant be a memory leak there. If I remove the function where I authenticate my server can handle as much clients as I want. If I authenticate every clients with connections to the database it crashes on PQconnectdb at th 1024th client without returning an error(it just segfaults). My ulimit -n is more than 1024 btw. Did anybody encounter such problem?? I've been trying to fix this one for a long time... Thanks -Mat -- Mathieu Dube Mondo-Live www.flipr.com
[SQL] Transactions in PLPGSQL?
Hi All! Are the BEGIN/END; seen in a typical PL/PGSQL function a transaction wrapper, or do I need to add another BEGIN/END block? Should I just put a 'rollback' in the function, or do I need to do something special? Thanks! -Ken
[SQL] parse error in create index
Hi,
could someone, please, explain me the following parse error?
adressen=> \d geburtstage
Table= geburtstage
+--+--+---+
| Field | Type|
Length|
+--+--+---+
| lfd_nr | int4
| 4 |
| geburtstag | date
| 4 |
+--+--+---+
adressen=> create index Monat_Tag on geburtstage (date_part('month',
Geburtstag));
ERROR: parser: parse error at or near "'"
adressen=>
Thanks in advance,
--
Hubert Palme
[EMAIL PROTECTED]
[SQL] "Subclassing" in SQL
I hope that title line is reasonably accurate. Here's what I'm trying to do, and would love it anyone can provide guidance. I have a table of utterances in a focus group setting; each record contains an identifier for the speaker and group, as well as the length of the utterance (in words) and then several boolean variables, each representing whether a substantive concept is present in the utterance or not. The trouble is that some of these concept variables (called 'codes') really are subsets of one another. For example, one code (called `cd_interest') is a particular instance of another code (called 'cd_pragmatic'). My question is whether there is any way to represent this relationship in SQL, without changing the underlying data. That is, I don't want to simply do: UPDATE statements SET cd_pragmatic = 't' WHERE cd_interest; because it's theoretically possible for me to change this conceptual relationship in the future. What I think I'm looking for is some sort of a join that will cause postgres to consider cd_pragmatic as True whenever cd_interest is true. Any thoughts? -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin [EMAIL PROTECTED] - [EMAIL PROTECTED]
Re: [SQL] RE: C function for use from PLpgSQL trigger
> You could send the column name directly into your c function. For example: > c_function_name(NEW.col1, NEW.col2, NEW.col3). Otherwise I am not sure how > to send NEW into a C function. You could try declaring NEW in your C > function as a tuple. Thanks for your reply. I was hoping that I could avoid hardcoding NEW.col1, etc, so that the function could be used for multiple relations. I've also tried to declare the input parameter to the function as a tuple, but PLpgSQL never gets that far -- it doesn't seem to support passing NEW as a parameter. Oh, well. I will probably just write all of my logic into a C function and skip PLpgSQL entirely. That's too bad because it would be far simpler (and preferrable IMHO) to write a generic trigger function in PLpgSQL and call C functions for only certain operations that PLpgSQL does not directly support. Joe
[SQL] Packages to Install
What packages do I need for PostgreSQL 7.xx? I went to rpmfind.net and there were too many files. I am running a Pentium. Can someone please tell me what packages I need to install POstgreSQL 7.xx. P.S I am going to have a fresh install Thanks
[SQL] CREATE TABLE AS and ORDER BY
Hi, I tried to use CREATE TABLE AS and ORDER BY. The query is as followed: create table freshhr21 as select e.studentid, u.hoursxfer from enrollmentstatus e, undergradclass u where e.studentid = u.studentid and e.classtd = '1' order by u.hoursxfer But, it returns error message "ERROR: parser: parse error at or near "order"". Does "create table as" support "order by" inside of it? I am using PostgreSQL 6.5.3. Thank you. Joy Chuang
[SQL] Search
Hi I hope someone can help me My problem: I have make a search machine whit: LIKE '%$suchbegriffe[$i]%' but when I search Test - the search machine shows only entries whit Test. But not test or tESt. (sorry for my bad english) Regards, Sebastian
Re: [SQL] Problem with Day of Week
Always- I think I'll use the to_char since I think you all are saying that that is ISO or at least POSIX. On Mon, 5 Feb 2001, Karel Zak wrote: > > On Mon, 5 Feb 2001 [EMAIL PROTECTED] wrote: > > > Ok, so there is actually two standards then. Is this documented > > anywhere? Is this is something that is going to change? I don't want > > to write and app and have things "break" during and upgrade :) > > I mean you can be caseful. Not changes planned here. > > date_part() is not documented to much in detail, but formatting > functions are described good. > > Karel >
RE: [SQL] PL/PGSQL function with parameters
Title: RE: [SQL] PL/PGSQL function with parameters
Just for the record:
DROP FUNCTION table_count(varchar);
CREATE FUNCTION table_count(varchar) RETURNS integer AS '
DECLARE
SQL varchar;
RES integer;
BEGIN
SQL = ''SELECT * INTO temp1 FROM '' || $1;
EXECUTE SQL;
SELECT count(*) INTO RES FROM temp1;
RETURN(RES)
END;
'
LANGUAGE 'plpgsql';
...
dev=> select table_count('switch');
test
--
6
(1 row)
This function produces exactly what you would hope for, a count of rows in the specified table. It's particularly inefficient at doing it, because it does a table copy (and doesn't bother to clean up after itself ;-(), so don't do this on a large table ;-) but it shows the principle.
What I couldn't get it to do was to select directly into the variable RES. Perhaps someone could enlighten me.
Cheers...
MikeA
-Original Message-
From: David Richter [mailto:[EMAIL PROTECTED]]
Sent: 06 February 2001 09:39
To: Michael Ansley
Subject: Re: [SQL] PL/PGSQL function with parameters
Hello!
Thanks a lot for Your answer!
But with my version 7.0.2. this suggestion doesn't work:
It appears: parser: parse error at or near "exec" or
parser: parse error at or near "execute"
And how should i design the update command in the suggested way e.g.?
EXEC ''UPDATE '' ||$1
''SET '' || $2 '' = psr_rec.parentoid
WHERE chilioid = psr_rec.childoid;''
Wich exact release I will need to use this feature?
Wich one are You using?
Greetings
David
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: [SQL] Search
On Mon, 5 Feb 2001, Sebastian --[ www.flashhilfe.de ]-- wrote: > I have make a search machine whit: > > LIKE '%$suchbegriffe[$i]%' > > but when I search Test - the search machine shows only entries > whit Test. But not test or tESt. LIKE is case-sensitive. You should convert your column to uppercase: WHERE UPPER(field) LIKE ... or use case-insensitive regular expression: WHERE field ~* '' -- Brett http://www.chapelperilous.net/~bmccoy/ --- The Angels want to wear my red shoes. -- E. Costello
[SQL] SQL question
I'm trying to extract references (relationships) between tables for the purpose of reverse/forward engineer from a modeling tool called PowerDesigner. Here is the sql: select u.usename, p.relname, v.usename, c.relname, t.tgconstrname, dumpref(t.tgargs, 4), ** (I know this function does not exist in postgresql) dumpref(t.tgargs, 5) from pg_trigger t, pg_proc f, pg_class p, pg_class c, pg_user u, pg_user v where 1=1 andf.proname='RI_FKey_check_ins' andt.tgfoid=f.oid andc.oid=t.tgrelid andp.oid=t.tgconstrrelid andu.usesysid=p.relowner andv.usesysid=c.relowner; I always get the following message: ** << Error while executing the query; ERROR: Function 'dumpref(bytea, int4)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts >> Since integrity constraints are done using triggers, i makes sense that I can reverse engineer those FK constraint from the pg_trigger table. Unfortunately I don't know how to extract the information from the "tgargs" field. CAN ANYONE HELP ??
[SQL] Re: Search
force lower case or use the non case-senstitive search e.g.
lower(column) LIKE lower('%$suchbegriffe[$i]%')
or
column ~* '$suchbegriffe[$i]'
(no need for wildcards when using ~* it assumes %value%)
"Sebastian --[ www.flashhilfe.de ]--" <[EMAIL PROTECTED]> wrote in
message 95n58g$5fa$[EMAIL PROTECTED]">news:95n58g$5fa$[EMAIL PROTECTED]...
> Hi
>
> I hope someone can help me
>
> My problem:
>
> I have make a search machine whit:
>
> LIKE '%$suchbegriffe[$i]%'
>
> but when I search Test - the search machine shows only entries
> whit Test. But not test or tESt.
>
> (sorry for my bad english)
>
> Regards, Sebastian
>
>
[SQL] Re: Search
Thank you!!!
It works perfect !!
Regards, Sebastian
PM <[EMAIL PROTECTED]> schrieb in im Newsbeitrag:
95otrr$hjg$[EMAIL PROTECTED]
> force lower case or use the non case-senstitive search e.g.
>
> lower(column) LIKE lower('%$suchbegriffe[$i]%')
>
> or
>
> column ~* '$suchbegriffe[$i]'
> (no need for wildcards when using ~* it assumes %value%)
Re: [SQL] CREATE TABLE AS and ORDER BY
Joy Chuang <[EMAIL PROTECTED]> writes: > But, it returns error message "ERROR: parser: parse error at or near > "order"". Does "create table as" support "order by" inside of it? Evidently not. > I am using PostgreSQL 6.5.3. It seems to work in 7.0 and later. regards, tom lane
Re: [SQL] parse error in create index
Functional indexes cannot currently take constant values to the function,
so it's complaining about the constant 'month'. The current workaround is
probably to create a function that does the date_part('month', ) for
you and then use that function in the index creation.
On Sat, 3 Feb 2001, Hubert Palme wrote:
> Hi,
>
> could someone, please, explain me the following parse error?
>
> adressen=> \d geburtstage
> Table= geburtstage
> +--+--+---+
> | Field | Type|
> Length|
> +--+--+---+
> | lfd_nr | int4
> | 4 |
> | geburtstag | date
> | 4 |
> +--+--+---+
> adressen=> create index Monat_Tag on geburtstage (date_part('month',
> Geburtstag));
> ERROR: parser: parse error at or near "'"
> adressen=>
>
> Thanks in advance,
>
> --
> Hubert Palme
> [EMAIL PROTECTED]
>
Re: [SQL] PL/PGSQL function with parameters
Michael Ansley <[EMAIL PROTECTED]> writes: > CREATE FUNCTION table_count(varchar) RETURNS integer AS ' > DECLARE > SQL varchar; > RES integer; > BEGIN > SQL = ''SELECT * INTO temp1 FROM '' || $1; > EXECUTE SQL; > SELECT count(*) INTO RES FROM temp1; > RETURN(RES) > END; > ' > LANGUAGE 'plpgsql'; > What I couldn't get it to do was to select directly into the variable RES. I tried this, and it seems that "SELECT ... INTO foo" is not executed correctly by EXECUTE --- the INTO is handled as an ordinary select-into- table construct rather than plpgsql's select-into-variable. While I have not looked closely, I seem to recall that plpgsql handles INTO by stripping that clause out of the statement before it's passed to the SQL engine. Evidently that's not happening in the EXECUTE case. Jan, do you agree this is a bug? Is it reasonable to try to repair it for 7.1? If we do not change the behavior of EXECUTE now, I fear it will be too late --- some people will come to depend on the existing behavior. regards, tom lane
RE: [SQL] PL/PGSQL function with parameters
Title: RE: [SQL] PL/PGSQL function with parameters Yes, that was why I wrote it in the way that I did. The table is effectively given a constant name, and the count is got from the table with a known name. But of a kludge, but in 45sec, that was all I could come up with ;-) It would be VERY useful to see it fixed. Cheers... MikeA -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 06 February 2001 16:16 To: Michael Ansley Cc: Jan Wieck; sqllist; [EMAIL PROTECTED] Subject: Re: [SQL] PL/PGSQL function with parameters Michael Ansley <[EMAIL PROTECTED]> writes: > CREATE FUNCTION table_count(varchar) RETURNS integer AS ' > DECLARE > SQL varchar; > RES integer; > BEGIN > SQL = ''SELECT * INTO temp1 FROM '' || $1; > EXECUTE SQL; > SELECT count(*) INTO RES FROM temp1; > RETURN(RES) > END; > ' > LANGUAGE 'plpgsql'; > What I couldn't get it to do was to select directly into the variable RES. I tried this, and it seems that "SELECT ... INTO foo" is not executed correctly by EXECUTE --- the INTO is handled as an ordinary select-into- table construct rather than plpgsql's select-into-variable. While I have not looked closely, I seem to recall that plpgsql handles INTO by stripping that clause out of the statement before it's passed to the SQL engine. Evidently that's not happening in the EXECUTE case. Jan, do you agree this is a bug? Is it reasonable to try to repair it for 7.1? If we do not change the behavior of EXECUTE now, I fear it will be too late --- some people will come to depend on the existing behavior. regards, tom lane ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: [SQL] PL/PGSQL function with parameters
Tom, Jan, Michael, > While I have not looked closely, I seem to recall that plpgsql handles > INTO by stripping that clause out of the statement before it's passed to > the SQL engine. Evidently that's not happening in the EXECUTE case. > > Jan, do you agree this is a bug? Is it reasonable to try to repair it > for 7.1? If we do not change the behavior of EXECUTE now, I fear it > will be too late --- some people will come to depend on the existing > behavior. If you think that's the best way. What we're really all wanting is a wy in PL/pgSQL to pass a parameter as an object name. Doing it *without* using EXECUTE would be even better than modifying EXECUTE to accomdate SELECT ... INTO variable. If we can write queries that address tables by OID, that would give us a quick workaround ... get the OID from pg_class, then pass it to the query as variables of type OID: SELECT column1_oid, column2_oid FROM table_oid WHERE column2_oid = variable1 ORDER BY column1_oid; OF course, having PL/pgSQL do this automatically would be even better, but I suspect would require a *lot* of extra programming by Jan. And all of this should be influenced by whatever you guys are planning to do about Stored Procedures. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] CREATE TABLE AS and ORDER BY
Hey, Try: select e.studentid, u.hoursxfer into freshhr21 from enrollmentstatus e, undergradclass u where e.studentid = u.studentid and e.classtd = '1' order by u.hoursxfer Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 5 Feb 2001, Joy Chuang wrote: > Hi, > > I tried to use CREATE TABLE AS and ORDER BY. The query is as followed: > > create table freshhr21 as > select e.studentid, u.hoursxfer > from enrollmentstatus e, undergradclass u > where e.studentid = u.studentid and e.classtd = '1' > order by u.hoursxfer > > But, it returns error message "ERROR: parser: parse error at or near > "order"". Does "create table as" support "order by" inside of it? > > I am using PostgreSQL 6.5.3. > > Thank you. > > Joy Chuang >
Re: [SQL] Search
Hi, You seem want to match string insensitively, I guess. Try: ~* 'test' -- match Test|tEst|tESt ... ~* '.*test.*' -- match whateverTesTwhatever Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 5 Feb 2001, Sebastian --[ www.flashhilfe.de ]-- wrote: > Hi > > I hope someone can help me > > My problem: > > I have make a search machine whit: > > LIKE '%$suchbegriffe[$i]%' > > but when I search Test - the search machine shows only entries > whit Test. But not test or tESt. > > (sorry for my bad english) > > Regards, Sebastian > >
[SQL] type casting: varchar to date
Is there some way to do something like this ?? : crate table t ( a varchar(12), b date ); select (a::date-b) from t; ERROR: cannot cast type 'varchar' to 'date'. Thanks -- Fer
[SQL] timestamp- milliseconds since epoch output
Hi, I'm using a timestamp field called date_created. Whenever I select it I get: select date_created from tbl_user; date_created 2001-02-05 17:23:26-08 2001-02-05 17:45:39-08 2001-02-03 03:58:53-08 (3 rows) I've tried using variations of to_char and to_timestamp but can't seem to get the timestamp as a value of milliseconds since the Epoch (Jan 1, 1970). I've been looking through the user manual but can't seem to find anything... might be nice to put it in there as this is probably a very common operation. Any ideas? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5
Re: [SQL] type casting: varchar to date
On Tue, 6 Feb 2001, J.Fernando Moyano wrote:
>
> Is there some way to do something like this ?? :
Yes,
select to_timestamp('hello 02-06-2001', '"hello "MM-DD-');
Karel
Re: [SQL] timestamp- milliseconds since epoch output
On Tue, 6 Feb 2001 [EMAIL PROTECTED] wrote: > Hi, > > I'm using a timestamp field called date_created. Whenever I select it > I get: > > select date_created from tbl_user; > date_created > > 2001-02-05 17:23:26-08 > 2001-02-05 17:45:39-08 > 2001-02-03 03:58:53-08 > (3 rows) > > I've tried using variations of to_char and to_timestamp but can't seem > to get the timestamp as a value of milliseconds since the Epoch (Jan to_char() and to_timestamp() are milliseconds ignorant routines. It's in my TODO list for next release -- now we in feature freeze state. Karel
Re: [SQL] timestamp- milliseconds since epoch output
[EMAIL PROTECTED] writes:
> I've tried using variations of to_char and to_timestamp but can't seem
> to get the timestamp as a value of milliseconds since the Epoch (Jan
> 1, 1970).
regression=# select date_part('epoch','2001-02-05 17:23:26.123456-08'::timestamp);
date_part
--
981422606.123456
(1 row)
Multiply by 1000 if you feel a strong urge to have it in milliseconds...
regards, tom lane
Re: [SQL] Postgres-HOWTO
On Tue, 06 Feb 2001 08:50, Poet/Joshua Drake wrote: > Has been removed from the LDP website. Good news indeed! Now what are we going to do with it? Can the original document's source be made available so that somebody can do the needed work without having to re-key. There is a _lot_ of very good information in there buried underneath the ... um ... -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [SQL] Postgres-HOWTO
Hello, The Postgres team from PGSQL, Inc. has agreed to provide us with a new version. J On Wed, 7 Feb 2001, Christopher Sawtell wrote: >On Tue, 06 Feb 2001 08:50, Poet/Joshua Drake wrote: >> Has been removed from the LDP website. > >Good news indeed! > >Now what are we going to do with it? > >Can the original document's source be made available so that >somebody can do the needed work without having to re-key. > >There is a _lot_ of very good information in there buried underneath the >... um ... > > > -- -- CommandPrompt - http://www.commandprompt.com OpenDocs, LLC. - http://www.opendocs.org LinuxPorts - http://www.linuxports.com LDP - http://www.linuxdoc.org -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
[SQL] how to do plpgsql?
When trying to do some of the examples on http://www.postgresql.org/docs/postgres/c40914344.htm I keep getting: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. version is: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] how to do plpgsql?
Joseph, First you need to install plpgsql on a per database basis, or you can just install it on template1 and it will get added to all new databases. CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; -Josh Berkus (Instructions courtesy of Jeff at PGSQL Inc.) -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] how to do plpgsql?
Huh. You'd think this would be prominent in the documentation page at http://www.postgresql.org/docs/postgres/c4091.htm Thanks. Josh Berkus wrote: > > Joseph, > > First you need to install plpgsql on a per database > basis, or you can just install it on template1 and it > will get added to all new databases. > > CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER > "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; > > -Josh Berkus > > (Instructions courtesy of Jeff at PGSQL Inc.) > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 565-7293 >for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] plpgsql error: cache lookup from pg_proc failed
playpen=# create table aa( playpen(# a int, playpen(# b int, playpen(# t timestamp playpen(# ); CREATE playpen=# playpen=# playpen=# CREATE FUNCTION touch () RETURNS OPAQUE AS ' playpen'# BEGIN playpen'# new.t := current_timestamp; playpen'# RETURN new; playpen'# END; playpen'# ' LANGUAGE 'plpgsql'; CREATE playpen=# playpen=# playpen=# CREATE TRIGGER lastmod_1 AFTER update or insert ON aa FOR EACH ROW EXECUTE PROCEDURE touch(); CREATE playpen=# insert into aa (a, b) values (1,2); ERROR: plpgsql: cache lookup from pg_proc failed What does this error message mean? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Ross, Thanx for the heads up on this. The select did indeed return something other than four: 5. I updated as you suggested, but that alone didn't fix the problem. I'm updating tblFDBMono now with the same type of 'fix' to see if this is the root of the problem. Is '=' handled differently between PostgreSQL and MySQL in this case? -Brice "Ross J. Reedstrom" wrote: > > Brice - > What's the result of > > select monographid,length(monographid) from tblpemdruglink where > monographid ~ '^2008'; > > It occurs to me that your delimited text file may have padded values, > and "=" insists on exact matches for VARCHAR. > > update tblpemdruglink set monographid=btrim(monographid); > > might help, if the first query returns anything but 4. > > Ross > > On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote: > > Here's something to chew on ... > > > > The following snippet of SQL produces a result: > > > > > > > Please respond to this newsgroup, the mailing list (which should be > > mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my > > thumbs until I can figure this one out. > > > > Regards, > > Brice Ruth
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Unfortunately ... that didn't seem to help :( I used btrim on all the fields that were part of an equals (=) statement and reran the select and got the same result (0 rows). After I was in the process of updating the tables, I thought that this may fail ... since again, the 'manual join' of these two tables returns the correct results ... and it uses the same equality tests as the SQL join. I'm more than happy to keep tryin' different things, though ... anything to get to the bottom of this. Regards, Brice Ruth "Ross J. Reedstrom" wrote: > > Brice - > What's the result of > > select monographid,length(monographid) from tblpemdruglink where > monographid ~ '^2008'; > > It occurs to me that your delimited text file may have padded values, > and "=" insists on exact matches for VARCHAR. > > update tblpemdruglink set monographid=btrim(monographid); > > might help, if the first query returns anything but 4. > > Ross > > On Tue, Feb 06, 2001 at 05:22:59PM -0600, Brice Ruth wrote: > > Here's something to chew on ... > > > > The following snippet of SQL produces a result: > > > > > > > Please respond to this newsgroup, the mailing list (which should be > > mirrored on the newsgroup), and/or to me personally. I'm twiddlin' my > > thumbs until I can figure this one out. > > > > Regards, > > Brice Ruth
[SQL] RE: plpgsql error: cache lookup from pg_proc failed
Setting NEW in an AFTER update or insert trigger is not wise. Try using a before update trigger instead. -Original Message- From: Joseph Shraibman [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, February 06, 2001 6:35 PM To: [EMAIL PROTECTED] Subject:plpgsql error: cache lookup from pg_proc failed playpen=# create table aa( playpen(# a int, playpen(# b int, playpen(# t timestamp playpen(# ); CREATE playpen=# playpen=# playpen=# CREATE FUNCTION touch () RETURNS OPAQUE AS ' playpen'# BEGIN playpen'# new.t := current_timestamp; playpen'# RETURN new; playpen'# END; playpen'# ' LANGUAGE 'plpgsql'; CREATE playpen=# playpen=# playpen=# CREATE TRIGGER lastmod_1 AFTER update or insert ON aa FOR EACH ROW EXECUTE PROCEDURE touch(); CREATE playpen=# insert into aa (a, b) values (1,2); ERROR: plpgsql: cache lookup from pg_proc failed What does this error message mean? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] RE: plpgsql error: cache lookup from pg_proc failed
Michael Davis wrote: > > Setting NEW in an AFTER update or insert trigger is not wise. Try using a before >update trigger instead. > I still get the error message. > -Original Message- > From: Joseph Shraibman [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, February 06, 2001 6:35 PM > To: [EMAIL PROTECTED] > Subject:plpgsql error: cache lookup from pg_proc failed > > playpen=# create table aa( > playpen(# a int, > playpen(# b int, > playpen(# t timestamp > playpen(# ); > CREATE > playpen=# > playpen=# > playpen=# CREATE FUNCTION touch () RETURNS OPAQUE AS ' > playpen'# BEGIN > playpen'# new.t := current_timestamp; > playpen'# RETURN new; > playpen'# END; > playpen'# ' LANGUAGE 'plpgsql'; > CREATE > playpen=# > playpen=# > playpen=# CREATE TRIGGER lastmod_1 AFTER update or insert ON aa FOR EACH > ROW EXECUTE PROCEDURE touch(); > CREATE > playpen=# insert into aa (a, b) values (1,2); > ERROR: plpgsql: cache lookup from pg_proc failed > > What does this error message mean? > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] how to do plpgsql?
On Wed, 07 Feb 2001 14:18, Joseph Shraibman wrote: > Huh. You'd think this would be prominent in the documentation page at > http://www.postgresql.org/docs/postgres/c4091.htm Thanks from me for that one too. What about incorporating that particular functionality into the initdb program. Strikes me that this should be available by default as part of the installed product. On the other hand I might be missing something, if so I'd like to be informed. Thanks. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz ->> Please refrain from using HTML or WORD attachments in e-mails to me <<-
[SQL] Is this a bug, or is it just me?
Tom et al. Discovered this quirk in foriegn keys: In the preliminary version of a database, I added foriegn key constraints to a number of tables, linking them to a column in a shared reference table (status.status) that was only one-half of a composite primary key (and thus the values were not unique). When I tried to delete a row containing a "2" in the status column from the status relation, I received a Foreign Key violation error event though there were other "2"'s in the table still present. So ... is this a bug in forign key implementation, or just my fault for keying off a non-unique value? And, if the latter, is there a way I can construct a foreign key constraint that keys onto a view or query? Grazie! -Josh Berkus __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
Re: [SQL] PL/PGSQL function with parameters
> "DR" == David Richter <[EMAIL PROTECTED]> writes:
DR> Folks,
DR> I wrote that function, wich doesn't work. I want to hand over the name
DR> of the tables(relation_table, update_table) and a
DR> column(column_to_fill). The intention is, to use the function also with
DR> other tables(not hard coded).
DR> BUT this error appears :
DR> psql:restructure.sql:32: ERROR: parser: parse error at or near "$1"
DR> I didn't found any solution.
DR> I would be grateful , if I could get some more Examples(more than in the
DR> Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in
DR> PL/PGSQL - functions.
DR> I would be no less grateful if anybody give detailed suggestions.
DR> CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS
DR> integer AS '
DR> DECLARE
DR> relation_table ALIAS FOR $1;
DR> update_table ALIAS FOR $2;
DR> column_to_fill ALIAS FOR $3;
DR> psr_rec record;
DR> bound integer;
DR> i integer := 0;
DR> BEGIN
DR> FOR psr_rec IN SELECT * FROM relation_table LOOP
DR> UPDATE update_table
DR> SET column_to_fill = psr_rec.parentoid
DR> WHERE chilioid = psr_rec.childoid;
DR> i := i + 1;
DR> END LOOP;
DR> IF NOT FOUND THEN RETURN 1;
DR> ELSE RETURN i;
DR> END IF;
DR> END;
DR> ' LANGUAGE 'plpgsql';
DR> SELECT
DR> patient_study_restructure('relpatient_study000','study','patientoid');
DR> Anybody (Jan Wieck?) who can make some sugestions on
DR> the above will
DR> receive my enthusiastic gratitude.
DR> David
You _cannot_ use parameters value as table or column name inside
plpgsql function. So your construct SELECT * FROM relation_table (and
others similar) is wrong. The same in other words: you cannot make
dynamic queries by plpgsql. BUT! You can use EXECUTE statement which
exists in 7.1. Here is some doc:
EXECUTE {query-string}
where query-string is a string of type TEXT containing the query to be executed.
Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not
prepared
and saved just once during the life of the server. Instead, the query is prepared
each time the
statement is run. The query-string can be dynamically created within the procedure
to
perform actions on variable tables and fields.
The results from SELECT queries are discarded by EXECUTE unless SELECT INTO is
used to
save the results into a table.
An example:
EXECUTE ''UPDATE tbl SET ''
|| quote_ident(fieldname)
|| '' = ''
|| quote_literal(newvalue)
|| '' WHERE ...'';
This example shows use of the functions quote_ident(TEXT) and
quote_literal(TEXT). Variables containing field and table identifiers should be
passed to
function quote_ident(). Variables containing literal elements of the dynamic query
string
should be passed to quote_literal(). Both take the appropriate steps to return the
input
text enclosed in single or double quotes and with any embedded special characters
intact.
--
Anatoly K. Lasareff Email:[EMAIL PROTECTED]
http://tolikus.hq.aaanet.ru:8080Phone: (8632)-710071
Re: [SQL] Transactions in PLPGSQL?
> "KC" == Ken Corey <[EMAIL PROTECTED]> writes: KC> Hi All! KC> Are the BEGIN/END; seen in a typical PL/PGSQL function a transaction wrapper, KC> or do I need to add another BEGIN/END block? No, BEGIN & END in plpgsql function are not transaction control statemens, but elements of plpgsql language. KC> Should I just put a 'rollback' in the function, or do I need to do something KC> special? You _cannot_ use any transaction control statemens (commit, rollback) into plpgsql function. So all the function must be in transaction block. -- Anatoly K. Lasareff Email:[EMAIL PROTECTED] http://tolikus.hq.aaanet.ru:8080Phone: (8632)-710071
