[GENERAL] Alter column length
I want to alter the length of a column without dumping an re-creating the table. I found this method in the archives and was just wondering if there are any side effects... - update pg_attribute set atttypmod = [column_oid] where attname = '[column_name]' where attrelid = (select oid from pg_class where relname = '[table_name]'); - Will doing this cause any problems? -Dan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] version issue?
Excellent! That worked! Thank you once again Tom! I was under the impression that you couldn't use an alias in the ORDER BY. Obviously, I was mistaken. Thanks, -Dan - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> : "Dan Wilson" <[EMAIL PROTECTED]> writes: : > : Possibly some future release will actually do the right thing with ORDER : > : BY of an expression on the output columns, but right now it has to be an : > : output column, period. : : > OK... I tried adding UPPER(last_name) to the result column lists of both : > sides of the union and it still gives me the same error. : : You have to use the SQL-standard syntax for ORDER BY, ie column name or : number, no shortcuts: : : SELECT ..., UPPER(last_name) AS upper_last_name : UNION : SELECT ..., UPPER(last_name) AS upper_last_name : ORDER BY upper_last_name; : : or if you prefer, ORDER BY n where n is the ordinal number of the : column. : : regards, tom lane
Re: [GENERAL] version issue?
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
: "Dan Wilson" <[EMAIL PROTECTED]> writes:
: > I have the following query running on two different servers. It works
on
: > 7.0.3 and gives the following error on 7.1beta4.
:
: > ERROR: Attribute 'last_name' not found
:
: > Your SQL statement:
: > SELECT first_name, last_name, middle_name, u.uid, end_year
: > FROM user_info u, auth a
: > WHERE u.uid = pri_key AND a.auth_table = 'user_info' AND live = 't'
: > AND site_id IN ('214') AND u.end_year > date_part('year', date
'today') - 2
: > UNION
: > SELECT first_name, last_name, middle_name, u.uid, end_year
: > FROM user_info u, current c
: > WHERE u.uid = c.uid
: > ORDER BY UPPER(last_name)
:
: 7.0.3 does not really work in this example (didn't you ever eyeball the
: resulting sort order closely??). 7.1 does not support the example at
: all. The error message from beta4 is not very good, but beta5 says the
: right thing:
:
: ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
result columns
:
: Possibly some future release will actually do the right thing with ORDER
: BY of an expression on the output columns, but right now it has to be an
: output column, period.
:
: regards, tom lane
OK... I tried adding UPPER(last_name) to the result column lists of both
sides of the union and it still gives me the same error.
How can I fix this?
-Dan
Re: [GENERAL] version issue?
Ok... here's what I tried. I was mistaken in my beta version.
sib=# select version();
version
PostgreSQL 7.1beta3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
sib=# SELECT first_name, last_name, middle_name, u.uid, end_year FROM
user_info u, auth a WHERE u.uid = pri_key AND a.auth_table = 'user_info' AND
live = 't' AND mission_id IN ('36') AND u.end_year > date_part('year', date
'today') - 2 UNION SELECT first_name, last_name, middle_name, u.uid,
end_year FROM user_info u, current c WHERE u.uid = c.uid ORDER BY
UPPER(last_name);
ERROR: Attribute 'last_name' not found
sib=# SELECT first_name, last_name, middle_name, u.uid, end_year FROM
user_info u, auth a WHERE u.uid = pri_key AND a.auth_table = 'user_info' AND
live = 't' AND mission_id IN ('36') AND u.end_year > date_part('year', date
'today') - 2 UNION SELECT first_name, last_name, middle_name, u.uid,
end_year FROM user_info u, current c WHERE u.uid = c.uid ORDER BY
UPPER(u.last_name);
ERROR: Relation 'u' does not exist
sib=# SELECT first_name, last_name, middle_name, u.uid, end_year FROM
user_info u, auth a WHERE u.uid = pri_key AND a.auth_table = 'user_info' AND
live = 't' AND mission_id IN ('36') AND u.end_year > date_part('year', date
'today') - 2 UNION SELECT first_name, last_name, middle_name, u.uid,
end_year FROM user_info u, current c WHERE u.uid = c.uid ORDER BY
UPPER(user_info.last_name);
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
result columns
Is this a bug in beta3? One of those combinations should work!
-Dan
- Original Message -
From: "Alfred Perlstein" <[EMAIL PROTECTED]>
> * Dan Wilson <[EMAIL PROTECTED]> [010225 22:47] wrote:
> > I have the following query running on two different servers. It works
on
> > 7.0.3 and gives the following error on 7.1beta4.
> >
> > ERROR: Attribute 'last_name' not found
> >
> > Your SQL statement:
> > SELECT first_name, last_name, middle_name, u.uid, end_year
> > FROM user_info u, auth a
> > WHERE u.uid = pri_key AND a.auth_table = 'user_info' AND live = 't'
> > AND site_id IN ('214') AND u.end_year > date_part('year', date
'today') - 2
> > UNION
> > SELECT first_name, last_name, middle_name, u.uid, end_year
> > FROM user_info u, current c
> > WHERE u.uid = c.uid
> > ORDER BY UPPER(last_name)
> >
> > I'm figuring it is in the ORDER BY, but I'm not sure. Can anyone help
me
> > out?
>
> (just a guess) try adding the table name, like: 'u.last_name'.
>
> --
> -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
>
Re: [GENERAL] Grant on Database?
> Hey All,
>
> We have a need to grant privileges on entire databases to users and/or
> groups. It looks like GRANT just grants on tables and sequences, but I'd
> like to know if there's a more direct way to do it. What I'm doing now
> is getting a list of tables and sequences and calling grant for each one
> in turn. How am I getting this list (I'm user Perl, not psql)? With this
> query:
>
> SELECT relname
> FROM pg_class
> WHERE relkind IN ('r', 'S')
>AND relowner IN (
>SELECT usesysid
>FROM pg_user
>WHERE LOWER(usename) = 'myuser')
>
> Anyway, pointers to any shortcuts for this would be greatly appreciated.
First pointer, phpPgAdmin (http://www.greatbridge.org/project/phppgadmin)
has this built into it. It will automatically get the list of tables,
sequences and views and run a grant statment on them.
Second pointer. GRANT will take multiple "relations" seperated by commas:
GRANT ALL ON table1, table1, seq1, seq2, view1, view2 TO my_user;
-Dan
Re: [GENERAL] order of clauses
: SELECT a.x/b.y FROM vals a, (SELECT y FROM vals WHERE y > 0) b WHERE (a.x : / b.y) > 1; How much of a performance hit is there when using a select in the FROM clause? Is it even noticeable? How much better is it to create a static view? -Dan
[GENERAL] Trapping NOTICE using PHP
Is there any way to trap the NOTICE output (specifically in PHP)? I would like to impliment an EXPLAIN feature in phpPgAdmin and have been unsuccessful in finding a way to do this because all the information returned by EXPLAIN is in a NOTICE. Any ideas? -Dan
Re: [GENERAL] Re: PostreSQL SQL for MySQL SQL
: I think it's still a good idea to provided the "if exists" clause in a : future PostgreSQL version for these reasons: : 1. it's convenient : 2. it doesn't interfere with existing functionality or performance : 3. it makes porting from MySQL to PostgreSQL easier I second this! It should probably be easy functionality to add (although I'm no C guru and definetly don't know the ins and out of postgres). I've made an equivalent in PHP, but it would be much easier if I could use it within pgsql dump/import scripts. Plus it requires an additional call to the database. -Dan
Re: [GENERAL] Re: [HACKERS] Re: Re: grant privileges to a database [URGENT]
A step in the right direction for this to have the system catalog have pg_user_* views. So dor databases we have: create view pg_user_database as select * from pg_database where pg_get_userbyid(datdba) = CURRENT_USER Of course, this doesn't account for superusers, but I'm sure there is a way the gurus can accomplish that. -Dan - Original Message - From: "Mike Miller" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, February 05, 2001 8:04 PM Subject: [GENERAL] Re: [HACKERS] Re: Re: grant privileges to a database [URGENT] > Hrm- I'd love to know where this patch is. I don't see how that quite > breaks PG_DUMPALL though. Really if your logged in as a superuser > (postgres) you should be able to use all the databases and dump all of the > data. Am I the only one that doesn't see where the problem is? How about a > patch that says 'if the user that created the database is not the current > user, then reject- otherwise accept'. I could go for that. Though access > control would be nice, I could log in as a superuser, make a user with the > ability to make databases, login as that user, make the databases I need, > then login as postgres and revoke the privilages of creating databases. > Suddenly you can only access databases you created and its as easy as that > (a few PHP lines if you ask me) to make new databases. Wouldn't it just be > a simple IF statement to see if the current user is the database owner [or > if they have the superuser ID set]? > > Am I not seeing the big picture? > > -- > Mike > > > >From: Kovacs Baldvin <[EMAIL PROTECTED]> > >To: Mike Miller <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED], [EMAIL PROTECTED], > >[EMAIL PROTECTED],[EMAIL PROTECTED] > >Subject: Re: [HACKERS] Re: Re: grant privileges to a database [URGENT] > >Date: Mon, 5 Feb 2001 20:13:38 +0100 (MET) > > > >Hello > > > >A few weeks ago I was interested in this question. My results were: > >- Yes, this is a sorrowful but true fact that if you enable access to > > someone to a database, she is automatically enabled to create > > objects in it. > >- Yes, the developers know it, and they said: there is a patch existing > > to workaround it. > >- No, they don't include it in 7.1. The reason: if you use that patch, > > pg_dumpall will not work. If somebody will have the strength in > > him to fix it, than it will be considered to include it in the base. > > > >After collecting these informations from more experienced people, > >I calmed down. Since I am in the beginning of creating my project, > >I think for the time when I will need it, it will be ready. > > > >Anyway, I do not know where this patch is. If you don't bother > >about pg_dumpall, ask a developer (a am only a wannabe developer) > >about it. > > > >If anyone detects that I wrote silly things, please do correct me. > > > >Bye, > >Baldvin > > > > > > > > > > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. >
Re: [GENERAL] pg_dump shell or php?
Here's a simple PHP script that I use. You can backup only certain
databases or the whole server. I'm actually planning on making this part of
the phpPgAdmin package. You can specify how many days you want the backup
files to remain.
-Dan
#!/usr/bin/php -q
> $file_name");
}
} else {
system("$pg_dump_dir/pg_dumpall > $file_name");
}
// echo date("Y-m-d H:i:s T"), "\n";
$dirh = dir($data_dir);
while($entry = $dirh->read()) {
$old_file_time = (date("U") - $keep);
$file_created = filectime("$data_dir/$entry");
if ($file_created < $old_file_time && !is_dir($entry)) {
if(unlink("$data_dir/$entry")) {
// echo "Delete $data_dir/$entry\n";
}
}
}
?>
- Original Message -
From: "Matt Friedman" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, February 02, 2001 4:53 PM
Subject: [GENERAL] pg_dump shell or php?
Any one written a shell or php script that runs pg_dump on a db say everyday
or something?
Any suggestions?
thanks,
Matt Friedman
Re: [GENERAL] php as stored procedures
: As for whether it will be done, well, what does PHP give you over : Perl? I know Perl well and PHP AFAICS is a tiny subset of Perl : designed to be embedded in web pages. Given PL/Perl, do we really : need PL/PHP? I wouldn't call PHP a subset of Perl at all! I'd call them sibling languages with different strengths. I think Perl does certain things better than PHP but PHP has strengths that Perl probably can't compete with. But for the most part, AFAIK, anything you can do in Perl, you can also do in PHP. -Dan
[GENERAL] Weird View behavior
I've got some wierd behavior with a view that I created. devwindaily=# select version(); version - PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66(1 row) devwindaily=# \d email_num View "email_num" Attribute | Type | Modifier ---+-+-- user_emailaddress | varchar(50) | email_count | integer | View definition: SELECT user_info.user_emailaddress, count(user_info.user_emailaddress) AS email_count FROM user_info GROUP BY user_info.user_emailaddress; devwindaily=# select * from email_num; user_emailaddress | email_count -+- | 1 [EMAIL PROTECTED] | 1 asfdasdfadsfafdsaf | 1 [EMAIL PROTECTED] | 1 [EMAIL PROTECTED] | 3 [EMAIL PROTECTED] | 1 [EMAIL PROTECTED] | 1 [EMAIL PROTECTED] | 1 | 1(9 rows) devwindaily=# select * from email_num where email_count > 1; user_emailaddress | email_count -+- | 1 [EMAIL PROTECTED] | 1 asfdasdfadsfafdsaf | 1 [EMAIL PROTECTED] | 1 [EMAIL PROTECTED] | 3 [EMAIL PROTECTED] | 1 [EMAIL PROTECTED] | 1 [EMAIL PROTECTED] | 1 | 1(9 rows) devwindaily=# select * from email_num where email_count < 2; user_emailaddress | email_count ---+-(0 rows) devwindaily=# select * from email_num where email_count < 1; user_emailaddress | email_count ---+-(0 rows) devwindaily=# select * from email_num where email_count = 1; user_emailaddress | email_count ---+-(0 rows) Now what is going on?!?!? That just plain old doesn't make sense! Thanks for any help! -Dan
Re: [GENERAL] System tables
Descriptions of the system tables can be found here: http://www.postgresql.org/devel-corner/docs/postgres/catalogs.htm Also, if you want to muddle through some PHP code, you can get phpPgAdmin and checkout how the database and table structures are retrieved. It's all done with standard queries. phpPgAdmin can be downloaded from: http://www.greatbridge.org/project/phppgadmin -Dan > I'm working on a project which needs to introspect the structure of a > database. I need to fish out table names, attribute names and > type, and foreign keys. > > I had a poke about the system tables, but the meaning of the data isn't > always 100% clear. In particular, I could find no easy way to determine the > foreign key constraints (I know that Postgres emulates foreign keys with > rules and triggers). > > Does anyone have some definitive information about the contents of the > system tables. Something like a description of the attribute meanings and > an ER diagram explaining table links would be very helpful, and would lead > to faster more accurate hitchhiking. > > Cheers > > Chris
Re: [GENERAL] User Privileges
SELECT relacl FROM pg_class WHERE relname = '[table name]'
The relacl column contains specific information that must then be parsed to
determine the users which have access to a the table and the privileges
granted.
Example:
{"=","dan=arw","group developers=arw"}
The first section means the PUBLIC does not have any privileges on the
table. Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified
by a letter. r = select, a = insert, w = update/delete, R = rule. User
"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group
"developers" has the same privileges.
For more details --
http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
-Dan
- Original Message -
From: "W. van den Akker" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, December 26, 2000 11:57 PM
Subject: Re: [GENERAL] User Privileges
> How do I retrieve this privilages?
> I want to disable menu-options within a program. For that I have to
> retrieve the privilages for some tables.
>
> gr,
>
> Willem
>
> - Original Message -
> From: "Dan Wilson" <[EMAIL PROTECTED]>
> To: "Niral Trivedi" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, December 26, 2000 7:25 PM
> Subject: Re: [GENERAL] User Privileges
>
>
> > > For example I have 5 tables in database A. And now I want to give
> > > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But
according
> > to
> > > documentation, I have to execute 'GRANT' query 3 times(for
> > > select/update/insert) per table. meaning total of 15 times
> >
> > That's incorrect... you can do it all in one statement:
> >
> > GRANT select,update,insert TO "" ON
table_1,table_2,table_3,etc
> >
> > http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
> >
> > -Dan
> >
> >
> >
>
[GENERAL] phpPgAdmin 2.2.1 Released!
We are pleased to announce the release of phpPgAdmin version 2.2.1. Check us out at our new home @ http://www.greatbridge.org/project/phppgadmin. Feel free to use all the new functionality available through the greatbridge.org site (Thanks GreatBridge!) The new version includes many updates and fixes, as well as several new features including support for triggers and table ACL (priviledges). In addition, enhancements have been made to key functionality such as functions, database/table dumps and the advanced authentication. Download: http://www.greatbridge.org/project/phppgadmin/download/download.php Tar: ftp://ftp.greatbridge.org/pub/phppgadmin/stable/phpPgAdmin_2-2-1.tar.gz Zip: ftp://ftp.greatbridge.org/pub/phppgadmin/stable/phpPgAdmin_2-2-1.zip ChangeLog: http://pgdemo.acucore.com/ChangeLog -Dan Wilson phpPgAdmin Development Team [EMAIL PROTECTED] PS. If you would like to help us in development, please join greatbridge.org and request to become a member of our team.
[GENERAL] NEXTVAL function Bug
Ok... I think I found a bug... tell me if I'm smoking something on this:
I create a table with a mixed case name... everything works fine until I try
to use the sequence created with the SERIAL datatype.
test_db=# create table "mixed_Case" ("mix_id" SERIAL, "mix_var"
varchar(50));
NOTICE: CREATE TABLE will create implicit sequence 'mixed_Case_mix_id_seq'
for SERIAL column 'mixed_Case.mix_id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index
'mixed_Case_mix_id_key' for table 'mixed_Case'
CREATE
test_db=# \d "mixed_Case"
Table "mixed_Case"
Attribute |Type |Modifier
---+-+--
---
mix_id| integer | not null default
nextval('mixed_Case_mix_id_seq'::text)
mix_var | varchar(50) |
Index: mixed_Case_mix_id_key
test_db=# insert into "mixed_Case" (mix_id, mix_var) values
(nextval('mixed_Case_mix_id_seq'), 'not working');
ERROR: Relation 'mixed_case_mix_id_seq' does not exist
test_db=# insert into "mixed_Case" (mix_id, mix_var) values
(nextval('mixed_Case_mix_id_seq'::text), 'not working');
ERROR: Relation 'mixed_case_mix_id_seq' does not exist
test_db=# insert into "mixed_Case" (mix_id, mix_var) values
(nextval("mixed_Case_mix_id_seq"::text), 'not working');
ERROR: Attribute 'mixed_Case_mix_id_seq' not found
test_db=# insert into "mixed_Case" (mix_id, mix_var) values
(nextval("mixed_Case_mix_id_seq"), 'not working');
ERROR: Attribute 'mixed_Case_mix_id_seq' not found
test_db=#
I know I could just do a: insert into "mixed_Case" (mix_var) values ('not
working')
But this is for phpPgAdmin and so due to certain issues, I need to have the
nextval function work as it would without a mixed case table name.
Is this expected behavior?
-Dan
Re: [GENERAL] grant a db
Just recently added this functionality to phpPgAdmin. You can now update the ACL of all the objects (tables, sequences, views) of a database at the same time. This will be part of the next release (2.2.1) which should happen this week sometime. Look for the announcement. -Dan - Original Message - From: "Nick Fankhauser" <[EMAIL PROTECTED]> To: "Martin A. Marques" <[EMAIL PROTECTED]> Cc: "pgsql-general" <[EMAIL PROTECTED]> Sent: Tuesday, December 19, 2000 5:29 AM Subject: RE: [GENERAL] grant a db > I believe it has to be each table- Oracle has a nice "all tables" option > which might be worth the developer's consideration on the next round. I need > to do this regularly, so I'll probably see if I can write a tool to create a > SQL script. I'll let you know when I get it done. > > -Nick > > - > Nick Fankhauser > > Business: > [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > Personal: > [EMAIL PROTECTED] http://www.fankhausers.com > > > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On Behalf Of Martin A. > > Marques > > Sent: Tuesday, December 19, 2000 8:20 AM > > To: [EMAIL PROTECTED] > > Subject: [GENERAL] grant a db > > > > > > Maybe I'm confused, but can I GRANT a user with ALL privileges on > > a database, > > or does it have to do it on each table? > > > > -- > > System Administration: It's a dirty job, > > but someone told I had to do it. > > - > > Martín Marqués email: [EMAIL PROTECTED] > > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > > Administrador de sistemas en math.unl.edu.ar > > - > >
Re: [GENERAL] Auto incrementing fields. How?
Use the column type of SERIAL in your create table statement. This will automatically create a sequence and default for the given column. http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createsequence.htm for more on sequences. - Original Message - From: "Harry Wood" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 19, 2000 6:51 AM Subject: [GENERAL] Auto incrementing fields. How? > Anyone know how to create auto incrementing fields? > > -- > Harry > [EMAIL PROTECTED] > http://www.doc.ic.ac.uk/~hw97/Nojer2 > Nojer2 on chat.yahoo.com and zapidonia.com > ICQ number 18519769
Re: [GENERAL] newbie question:
ALTER USER postgres WITH PASSWORD '[enter password]' http://www.postgresql.org/users-lounge/docs/7.0/user/sql-alteruser.htm - Original Message - From: "Leon van Dongen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 18, 2000 1:56 AM Subject: [GENERAL] newbie question: > I have been expirementing with PostgreSQL > but now I have compiled PHP with PostgreSQL > I need an superuser to call the database from my scripts. > > Postgres is already defined as the superuser but how > can I set the superuser password ? > Is there a admin script just like MySQL or should > I insert it into the pg_??(group,user) table on the > default database ? > > please let me know > > > Leon
Re: [GENERAL] Trigger/Function problem
That was it! Thanks Tom. I just put this functionality into phpPgAdmin and of course it is taking the newline char from the browser's OS. Thanks for all your help! -Dan > "Dan Wilson" <[EMAIL PROTECTED]> writes: > > I get the following error: > > NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1 > > "RROR: parse error at or near " > > Just like that, eh? It looks like the parser is spitting up on a \r > in the function text. Try saving your script with Unix-style newlines. > > For 7.1 the plpgsql parser has been fixed to accept DOS-ish newlines, > but for now you gotta be careful... > > regards, tom lane
Re: [GENERAL] Trigger/Function problem
If you look at my function definition, you can see that this is not within the function body. This is the testing of the trigger which produces the error. It's just a plain old SQL statment that initiates the trigger. -Dan - Original Message - From: "Robert B. Easter" <[EMAIL PROTECTED]> > On Thursday 14 December 2000 21:27, Dan Wilson wrote: > > > > I'm totally fine up to this point... then I try this: > > > > UPDATE help SET site_id = 'APW' WHERE help_id = 2; > > > > I get the following error: > > > > NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1 > > "RROR: parse error at or near " > > Try: > > UPDATE help SET site_id = ''APW'' WHERE help_id = 2; > > Remember that ' is used to enclose the whole function body. You have to use > '' to mean a literal '. >
[GENERAL] Trigger/Function problem
I'm having problems with a trigger/function and I think it's actually a
system problem but I have no clue how to fix it. The trigger is supposed to
automatically timestamp the record when it is altered.
I've never used anything more than a sql function before so the plpgsql is
new to me.
Here's the info:
My table:
CREATE TABLE "help" (
"help_id" int4 DEFAULT nextval('help_id_seq'::text) NOT NULL,
"keyword" varchar(20) NOT NULL,
"help_text" text NOT NULL,
"auto_date" date NOT NULL,
"title" varchar(50) DEFAULT 'Help Topic',
"admin" bool DEFAULT 't',
"site_id" varchar(5) DEFAULT '0',
CONSTRAINT "help_pkey" PRIMARY KEY ("help_id")
);
My function:
CREATE FUNCTION "f_auto_date"() RETURNS OPAQUE AS '
BEGIN
NEW.auto_date := ''now'';
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
My trigger:
CREATE TRIGGER "t_auto_date" BEFORE INSERT OR UPDATE ON "help" FOR EACH ROW
EXECUTE PROCEDURE "f_auto_date"();
I'm totally fine up to this point... then I try this:
UPDATE help SET site_id = 'APW' WHERE help_id = 2;
I get the following error:
NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1
"RROR: parse error at or near "
I've tried the sample on the following web page as well:
http://www.postgresql.org/users-lounge/docs/7.0/user/c40874340.htm
And I get the exact same error (except of course the function name is
different). So I'm assuming that it's a problem in my system
configuration... I may be wrong.
I don't know how to fix this or even where to begin. I do have plpgsql
installed as a language. I'm running PG 7.0.2 on standard RedHat 7 ... not
sure of the Kernel.
Thanks for your help,
-Dan
Re: [GENERAL] unique indexes
Tom, Thanks for the update on this query. I'm not positive where I found this query, but I'm pretty sure it was for a v6.5x something. Anyway, thanks. phpPgAdmin has been updated. -Dan - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Dan Wilson" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, November 19, 2000 10:14 AM Subject: Re: [GENERAL] unique indexes > "Dan Wilson" <[EMAIL PROTECTED]> writes: > > Here is the query from phpPgAdmin that does what you are asking for: > > > SELECT > >... > >and > >( > > i.indkey[0] = a.attnum > > or > > i.indkey[1] = a.attnum > > or > > i.indkey[2] = a.attnum > > or > > i.indkey[3] = a.attnum > > or > > i.indkey[4] = a.attnum > > or > > i.indkey[5] = a.attnum > > or > > i.indkey[6] = a.attnum > > or > > i.indkey[7] = a.attnum > >) > >... > > > This was adapted from the psql source. Hope it's what you need. > > Actually I think it was borrowed from a very crufty query in the ODBC > driver. Aside from being ugly, the above-quoted clause is now wrong, > because indexes can have more than 8 keys since 7.0. This is how ODBC > finds matching keys and attributes now: > > SELECT ta.attname, ia.attnum > FROM pg_attribute ta, pg_attribute ia, pg_class c, pg_index i > WHERE c.relname = '$indexname' > AND c.oid = i.indexrelid > AND ia.attrelid = i.indexrelid > AND ta.attrelid = i.indrelid > AND ta.attnum = i.indkey[ia.attnum-1] > ORDER BY ia.attnum > > which is cleaner since it doesn't assume anything about the max > number of keys. > > regards, tom lane
Re: [GENERAL] unique indexes
Here is the query from phpPgAdmin that does what you are asking for:
SELECT
ic.relname AS index_name,
bc.relname AS tab_name,
a.attname AS column_name,
i.indisunique AS unique_key,
i.indisprimary AS primary_key
FROM
pg_class bc,
pg_class ic,
pg_index i,
pg_attribute a
WHERE
bc.oid = i.indrelid
and ic.oid = i.indexrelid
and a.attrelid = bc.oid
and bc.relname = '$table'
and
(
i.indkey[0] = a.attnum
or
i.indkey[1] = a.attnum
or
i.indkey[2] = a.attnum
or
i.indkey[3] = a.attnum
or
i.indkey[4] = a.attnum
or
i.indkey[5] = a.attnum
or
i.indkey[6] = a.attnum
or
i.indkey[7] = a.attnum
)
ORDER BY
index_name, tab_name, column_name;
This was adapted from the psql source. Hope it's what you need.
-Dan Wilson
- Original Message -
From: "Jason Davies" <[EMAIL PROTECTED]>
To: "Stephan Szabo" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, November 19, 2000 8:42 AM
Subject: [GENERAL] unique indexes
> Hi,
>
> Thankyou for your help with pg_trigger :)
>
> I am trying to list the indexes for a table. So far I've come up with this
SQL
> query:
>
> SELECT bc.relname AS TABLE_NAME,
> a.attname AS COLUMN_NAME,
> a.attnum as KEY_SEQ,
> ic.relname as PK_NAME
> FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> WHERE bc.relkind = 'r'
> and upper(bc.relname) = upper('tablename')
> and i.indrelid = bc.oid
> and i.indexrelid = ic.oid
> and ic.oid = a.attrelid
> ORDER BY table_name, pk_name, key_seq;
>
> I need to extend it slightly to get whether each key is unique or not. Any
> ideas on how this might be done?
>
> I would be grateful for any help.
> Thanks,
> Jason Davies.
>
> =
> Jason Davies,
>
> _ _ _|_ _ _ _ _| _| www.netspade.com
> | |(/_ | _\|_)(_|(_|(/_ | programming tutorials
>| | programming community
> --- | programming news
>
> __
> Do You Yahoo!?
> Yahoo! Calendar - Get organized for the holidays!
> http://calendar.yahoo.com/
[GENERAL] DB and Table Permissions
Is there a reason why _any_ user can create a table on a database? Even if they do not own or have any permissions to it? I don't think that should happen. Is there a specific reason why it does? -Dan Wilson
[ANNOUNCE] phpPgAdmin 2.0.1 released
Do you use PostgreSQL and PHP? phpPgAdmin is a port of the ever popular phpMyAdmin for MySQL. Most of the functionality available through the MySQL version has been successfully ported for use on Postgres (with a few Postgres specific features). Features include: * create and drop databases * create, copy, drop and alter tables/views/sequences/functions * edit and add columns (to the extent Postgres allows) * execute any SQL-statement, even batch-queries * manage keys on fields * create and read dumps of tables * create csv exports of table data * administer one single database or * administer multiple servers * administer postgres users View a live demo or download the source at http://www.phpwizard.net/phpPgAdmin. Quotes from users/reviewers: ** "This is IMHO a very good port." -- Tobias Ratschiller ** "[PostgresAdmin] started out as a quick hack to get something up for my colleagues, and it is really shaping up to be a quite useful tool" -- Rob Casson (original developer of PostgresAdmin -- the first port) ** "I use [phpPgAdmin] everyday!" -- Marcellus Barrus If you have questions or comments concerning phpPgAdmin, please visit the site or contact me directly. -Dan Wilson [EMAIL PROTECTED] http://www.phpwizard.net/phpPgAdmin
Re: [GENERAL] Query buffer problem.
Semicolon (;) at the end of each line should fix it. -Dan > Hi, > > I insert 10,000 data into PostgreSQL database. I got the problem > while inserting: > > query buffer max length of 2 exceeded > query line ignored > . > query buffer max length of 2 exceeded > query line ignored > > > '); > PQsendQuery() -- query is too long. Maximum length is 8191 > query buffer max length of 2 exceeded > query line ignored > Segmentation fault (core dumped) > > > Would anyone know how to solve this problem, thanks in advance. > > -Albert > > > __ > Get Your Private, Free Email at http://www.hotmail.com >
[GENERAL] escaping wildcard chars
I am trying to pull in the different types allowed by postgres for a create function statement and am having problems with the types that start with an underscore (_). Because the underscore is the wildcard for a single character, I cannot perform the following query correctly. SELECT typname from pg_type WHERE NOT LIKE '_%' It gives me an empty set. Is there any way to escape the underscore. I tried to use '\_%', but that didn't help. Any suggestions? -Dan
Re: [GENERAL] pg-dump -- primary Key
I understand this, but does it set the indisprimary flag in the pg_index table? The reason I ask is because I am writing a web based app to administer a pgsql database and am attempting to keep track of the indices/keys. This is the property I am looking at determine whether the index is a primary key. Is there a better property or flag to examine for this purpose? -Dan > On Sun, Jul 25, 1999 at 03:13:39PM +1000, Chris Bitmead wrote: > > Dan Wilson wrote: > > > > > Yes, I am aware that the primary key does not really mean anything except > > > implicitly making it a unique key, but it's supposed to be there for > > > compatibility and it's not even in the dump. > > > > Someone mentioned recently that primary key enforces nulls as unique > > whereas unique index doesn't. > > > > Actually, I belive it enforces NOT NULL on primary keys, which it also > dumps in the pg_dump output. > > Ross
[GENERAL] pg-dump -- primary Key
I was looking at the pg_dump and realized that it does not indicate the primary keys. Why is this? It does dump the primary keys with the indexed, however as far as I could tell, it did not indicate anything as the primary key. Yes, I am aware that the primary key does not really mean anything except implicitly making it a unique key, but it's supposed to be there for compatibility and it's not even in the dump. It's not a huge deal, but I was wondering if there was reasoning behind it of which I am unaware. -Dan
Re: [GENERAL] alter table add column is broken in 6.5
It's not too practical to insert a new row into the system table that
handles the defaults, but here it goes. The table you are looking for is
pg_attrdef and contains the following columns.
adrelid -- The relation id for the table of the default value
adnum -- The column number of the default column
adbin -- The source (what returns the default value when a row is inserted)
adsrc -- The actual default value that you give postgres (ie nextval
('sequence'))
If you have another default that is exactly the same then I think it is
possible. You should figure out the relid of the table you are working on
and the column number is not difficult.
To obtain the table relid run this query:
select relname, oid from pg_class where relname = ''
The oid is the value for which you are looking. Then the column (adnum) id
is just the in order column number when you do a \d . Then be
sure to copy the adbin and adsrc from an identical default.
Hopefully that helped.
I have never tried this and so if any of you know that this won't work or if
you know that I have made some mistakes in my judgement, then please let me
know. I am a fairly new beginner at postgres (about 2 months).
-Dan Wilson
Bryan White wrote:
> I posted this yesterday and got no response, so I will try again.
>
> Alter table add column does not set the default value for new rows added
to
> a table. I can except that it does not copy the new default value for
> existing rows. That is easy to work around. My problem is that the
database
> does not reflect the default value for new rows added after the alter
> statement.
>
> I could work around this is someone could tell me how to modify the system
> tables to specify a default value. This does not seem to be much
> documentation for the layout of the system tables.
>
> Bryan White
> ArcaMax Inc.
> Yorktown VA
> www.arcamax.com
