[SQL] FW: Sharing Databases
Need help desperately. Is is possible at all to share tables between different databases. In SELECT query need to make join to two tables in different databases. Thanks a million in advance. Yoghini
[SQL] problem with keyword 'old'
I am ussing Postgresql v. 6.3 in a RedHat Linux system. I try to create a
rule to log the deletes on table 'filter' ussing the following:
CREATE RULE log_delete_filter AS ON DELETE TO filter
DO
INSERT INTO log_activity (mod_table, mod_record, mod_type)
VALUES ('filter', old.did, 'D');
The atributes of tables are:
filter (did int4, text text)
log_activity (mod_table char(20), mod_rec int4, mod_type char(1) )
I have the following result:
ERROR: old: Table does not exist.
and the rule is not created.
I tested a same rule on INSERT with the keyword new.did and is working
perfect.
Also I tested the commands:
SELECT new.did;
> ERROR: NEW used in non-rule query
SELECT current.did;
>ERROR: CURRENT used in non-rule query
SELECT old.did;
>ERROR: old: Table does not exist.
What is going on? Do you have any idea why does system not undestand
keyword 'old'?
Sincerely,
Basilis Kladis <[EMAIL PROTECTED]>
&Bgr;&agr;&sgr;&iacgr;&lgr;&eegr;&sfgr; &Kgr;&lgr;&aacgr;&dgr;&eegr;&sfgr;
Language Engineering Dept.
KNOWLEDGE S.A.
Re: [SQL] 7.0.3 BUG
> >> ERROR: copy: line 3910, Bad timestamp external representation > >> '2000-01-05 00:00:60.00+08' > >> Weird because those timestamps were generated by default now(). ... > Is there a work-around to this aside from manually changing the dump file? > Distribution Version: Linux Mandrake release 7.2 (Odyssey) for > i586 > It was shipped with Mandrake-Linux 7.2 > >> migrate=# select version(); > >> version > >> --- > >> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3 ... > We can be sure that the compiler is relatively bug free because it was > used to recompile the entire Linux distribution... Ah ha (or rather, ha ha ha)! I'd suggest using the RPMs posted on the postgresql.org ftp site, which include a sample .rpmrc file which fixes disasterous bugs in Mandrake's default compiler settings for building RPMs. Specifically, Mandrake sets the -ffast-math flag, which the gcc folks warn is not compatible with -On optimizations. When I build RPMs I kill the fast-math option, and the rounding troubles go away. The rounding trouble does not show up on other platforms or Linux distros because no one else ignores the gcc recommendations to this extent :( - Thomas
Re: [SQL] CREATE OR REPLACE VIEW
As a work around, you might get off telling php to ignore the errors, using error_reporting(); CREATE OR REPLACE cannot be found in the postgres docs, so I guess it is not there? /Palle Ricardo Dias Marques wrote: > > Hi all, > > Is CREATE OR REPLACE VIEW available in any way, in PostgreSQL 7.0.2?
[SQL] String function page incorrect?
I'm trying to find the correct function that returns the location of a
substring within a string. Looking at
http://www.postgresql.org/docs/user/x2731.htm, it gives the Function name as
"textpos" but in the Example column it uses "position". But neither one
works!
bf2=# select position('high', 'ig');
ERROR: parser: parse error at or near ","
bf2=# select textpos('high', 'ig');
ERROR: Function 'textpos(unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
bf2=# select textpos(text('high'), text('ig'));
ERROR: Function 'textpos(text, text)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
???
-Jonathan
[SQL] I get an error with Foreign Keys
I have around 40 tables defined, many relying on data in others so I have foreign key constraints. However, whenever I try to delete from any table, I get: ERROR: SPI_execp() failed in RI_FKey_cascade_del() What generates this? I need assistance in knowing where to begin. I checked again, and I could not find any circular foreign key constraints... Any help on where to search is appreciated. Thanks, --Brian
Re: [SQL] I get an error with Foreign Keys
On Thu, 30 Nov 2000, Brian Powell wrote: > I have around 40 tables defined, many relying on data in others so I have > foreign key constraints. However, whenever I try to delete from any table, > I get: > > ERROR: SPI_execp() failed in RI_FKey_cascade_del() > > What generates this? I need assistance in knowing where to begin. I > checked again, and I could not find any circular foreign key constraints... Hmm, that might mean that the statement being generated inside the trigger is incorrect in some way that isn't being reported. Can you send a schema dump of your tables and I'll see if I can reproduce it. BTW: The code in question is probably in src/backend/utils/ri_*.c [I can't remember the exact ending of the filename]. It should be building a statement and attempting to execute it using the SPI interface and it looks like the execute is failing I'd guess by the message.
Re: [SQL] String function page incorrect?
"Jonathan Ellis" <[EMAIL PROTECTED]> writes:
> I'm trying to find the correct function that returns the location of a
> substring within a string. Looking at
> http://www.postgresql.org/docs/user/x2731.htm, it gives the Function name as
> "textpos" but in the Example column it uses "position". But neither one
> works!
Yeah, that seems to be a few versions out of date :-(
The page should probably refer to "strpos", which is the true function
name:
play=> select strpos('high','ig');
strpos
2
(1 row)
You can also use the SQL92 POSITION syntax:
play=> select position('ig' in 'high');
strpos
2
(1 row)
regards, tom lane
Re: [SQL] String function page incorrect?
Jonathan Ellis wrote:
>
> I'm trying to find the correct function that returns the location of a
> substring within a string. Looking at
> http://www.postgresql.org/docs/user/x2731.htm, it gives the Function name as
> "textpos" but in the Example column it uses "position". But neither one
> works!
>
> bf2=# select position('high', 'ig');
> ERROR: parser: parse error at or near ","
You need SELECT position('ig' in 'high');
lbn=# select position('ig' in 'high');
strpos
2
(1 row)
-Roberto
--
Computer ScienceUtah State University
Space Dynamics Laboratory Web Developer
USU Free Software & GNU/Linux Club http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto
[SQL] eject
eject --MIME Multi-part separator--
RE: [SQL] a script that queries database periodically
Hi! You can compile PHP without HTTP server support then you get php exec 'shell' and: #!/usr/local/bin/php > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Bernie Huang > Sent: Monday, November 27, 2000 8:23 PM > To: PHP_DB; PHP_General; PGSQL-SQL; PGSQL-GENERAL > Subject: [SQL] a script that queries database periodically > > > Hello, > > My boss wants me to write a script that will send a email report to him > monthly with the result of database queries. > > For example, an email would be like: > > Monthly report > +--+-+---+ > | Vehicles | Mileage | # of Signouts | > +--+-+---+ > | Vehicle1 | 10324 | 5 | > +--+-+---+ > | Vehicle2 | 19384 | 6 | > +--+-+---+ > ... > > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? > > Thanks. > > - Bernie >
[SQL] Help with Procedures in pgsql
I have the folowing procedure in PL/pgsql: CREATE FUNCTION set_counter () RETURNS INT AS ' DECLARE nivel_rec record; new_count integer; BEGIN UPDATE nivel SET count=-2; FOR nivel_rec IN SELECT * FROM nivel LOOP new_count := set_counter_row (nivel_rec); END LOOP; RETURN new_count; END;' LANGUAGE 'plpgsql'; I create it, but when I do 'select set_counter()', it says IFX=# select set_counter(); ERROR: Attribute 'nivel_rec' not found Isn't that the right sintax to declare variables in pgsql? What's wrong with it? Does my procedure contain any other errors that will appear later? Thanks -- Nelio Alves Pereira Filho IFX Networks Sao Paulo / Brazil
Re: [SQL] Help with Procedures in pgsql
On Monday 27 November 2000 15:01, Nelio Alves Pereira Filho wrote: > I have the folowing procedure in PL/pgsql: > > CREATE FUNCTION set_counter () RETURNS INT AS ' > DECLARE > nivel_rec record; > new_count integer; I always used the syntax like: nivel_rec nivel%ROWTYPE; Not sure it matters though. > BEGIN > UPDATE nivel SET count=-2; > > FOR nivel_rec IN SELECT * FROM nivel LOOP > new_count := set_counter_row (nivel_rec); > END LOOP; I don't know what set_counter_row() is doing so the problem could be in that function. > > RETURN new_count; > END;' > LANGUAGE 'plpgsql'; > > I create it, but when I do 'select set_counter()', it says > IFX=# select set_counter(); > ERROR: Attribute 'nivel_rec' not found > > Isn't that the right sintax to declare variables in pgsql? What's wrong > with it? > Does my procedure contain any other errors that will appear later? > > > > Thanks -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
[SQL] Re: [PHP] a script that queries database periodically
> I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? if you know perl, you should check out DBI. Read all about it at www.cpan.org. It's pretty similar to the php database interface.
[SQL] Re: [PHP] a script that queries database periodically
Hi! I think you have installed GET , so use in your cronab GET http://localhost/report_for_my_boss/create.php |mail -s "Hello Boss" [EMAIL PROTECTED] if you miss get try it with lynx: lynx -source -dump http://localhost/.. |mail regards Folke -- Folke Ashberg Stage Internet Services GmbH Technical Development [EMAIL PROTECTED] [EMAIL PROTECTED] | Tel: +49 89 454591-77 | Fax: +49 89 454591-71 Kreillerstr. 21 | 81673 Munich | Germany | www.fanstage.de On Mon, 27 Nov 2000, Bernie Huang wrote: > Hello, > > My boss wants me to write a script that will send a email report to him > monthly with the result of database queries. > > For example, an email would be like: > > Monthly report > +--+-+---+ > | Vehicles | Mileage | # of Signouts | > +--+-+---+ > | Vehicle1 | 10324 | 5 | > +--+-+---+ > | Vehicle2 | 19384 | 6 | > +--+-+---+ > ... > > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? > > Thanks. > > - Bernie > >
Re: [HACKERS] Re: [SQL] Rules with Conditions: Bug, or Misunderstanding
On Wednesday 29 November 2000 19:42, Tom Lane wrote: > > Hm. Perhaps the "cannot update view" test is too strict --- it's not > bright enough to realize that the two rules together cover all cases, > so it complains that you *might* be trying to update the view. As the > code stands, you must provide an unconditional DO INSTEAD rule to > implement insertion or update of a view. The idea was to check just before the update occurred to see if the destination was view. Maybe the test is too high up, before all rewriting occurs. It is in InitPlan, the same place we check to make sure that we are not changing a sequence or a toast table. (actually initResultRelInfo called from InitPlan). I gathered from the backend flowchart that this wasn't called until all rewriting was done. Was I wrong? If all rewriting _is_ done at that point, why is the view still in the ResultRelInfo ? -- Mark Hollomon
Re: [HACKERS] Re: [SQL] Rules with Conditions: Bug, or Misunderstanding
Mark Hollomon <[EMAIL PROTECTED]> writes: > On Wednesday 29 November 2000 19:42, Tom Lane wrote: >> Hm. Perhaps the "cannot update view" test is too strict --- it's not >> bright enough to realize that the two rules together cover all cases, >> so it complains that you *might* be trying to update the view. As the >> code stands, you must provide an unconditional DO INSTEAD rule to >> implement insertion or update of a view. > It is in InitPlan, the same place we check to make sure that we are > not changing a sequence or a toast table. (actually initResultRelInfo > called from InitPlan). I gathered from the backend flowchart that this > wasn't called until all rewriting was done. Was I wrong? The rewriting is done, all right, but what's left afterward still has references to the view, because each rule is conditional. Essentially, the rewriter output looks like -- rule 1 if (rule1 condition holds) -- rule 2 applied to rule1 success case if (rule2 condition holds) apply rule 2's query else apply rule 1's query else -- rule 2 applied to rule1 failure case if (rule2 condition holds) apply rule 2's query else apply original query If the system were capable of determining that either rule1 or rule2 condition will always hold, perhaps it could deduce that the original query on the view will never be applied. However, I doubt that we really want to let loose an automated theorem prover on the results of every rewrite ... regards, tom lane
