[GENERAL] psql bug?
I am running postgres 6.5.3 on an IRIX6.5. In psql, I can't describe any table I create. I am able to select contents. Is this a bug? psql mydb mydb=> \d Database= bcams +--+--+--+ | Owner | Relation | Type | +--+--+--+ | dusty| sites| table| | dusty| sites_pkey | index| +--+--+--+ mydb=> \d sites ERROR: typeidTypeRelid: Invalid type - oid = 0 mydb=> select * from sites mydb-> ; id|site_name |first| second|category_code --++-+-+- -1|TRI-CITY AIRPORT|44.111000|18.111000|1 -2|USED CAR LOT|44.222000|18.222000|1 -3|BIG CITY MALL |44.333000|18.333000|2 -4|TOWN DUMP |44.444000|18.444000|1 -5|VILLAGE PARK NE |44.555000|18.555000|1 (5 rows)
Re: [GENERAL] how to use pg-connect ?
I would like more info about the pgtcl commands, too. In the "Integrated Document" on the postgres web page, there's a list of commands (chapter 49), but not detailed information. When I enter pgtclsh, I can get a list of options. For pg_connect, I get this: % pg_connect pg_connect: database name missing pg_connect databaseName [-host hostName] [-port portNumber] [-tty pgtty]] pg_connect -conninfo This is fairly intuitive, but the pg_result command isn't as obvious: % pg_result Wrong # of arguments pg_result result ?option? where option is -status -error -conn -oid -numTuples -numAttrs -assign arrayVarName -assignbyidx arrayVarName ?appendstr? -getTuple tupleNumber -tupleArray tupleNumber arrayVarName -attributes -lAttributes -clear Is there a description of these options in one of the other documents? Sarah Officer [EMAIL PROTECTED] Cécile DESNOYERS wrote: > > Hello, > > I'd like to know if there is a way to use pg_connect() with a > different host than « localhost ». > I tried to put an IP address or a server name but it didn't work. Has > anybody ever used pg_connect with something else than localhost ? > > Thank you > > Cecile > >
[GENERAL] < and > for datetime
Hi all, I'm trying to find less than and greater than functions for datetime types. It doesn't look the the time interval operators would be appropriate, but I haven't found anything else in the docs. Here's my snippet of plpgsql which fails: select count(*) into rescount from currentresults cr, masters m where cr.ben = new.ben and m.mi_id = cr.mi_id and m.idate > mydate; rescount & mydate are variables. How do I compare dates? I'd be glad to read the documentation if I could find the right page. Thanks, Sarah [EMAIL PROTECTED]
[GENERAL] psql parsing and variable problems
I have been looking at the psql documentation posted on the web
site. I am trying to set variables, but I can't seem to make that
happen. I also notice that psql doesn't seem to parse full
backslash commands. Are the documents out of date, or is the a bug?
\echo foo
is interpreted as \e and pops me into the editor.
\qecho foo
is interpreted as \q and exits psql.
When I try to use a variable, psql doesn't seem to recognize it.
> create table status (
> code varchar(5) not null,
> stat_desc varchar(30) not null);
CREATE
> insert into status(code, stat_desc) values ('A1', 'First Status');
INSERT 19082 1
> select * from status;
code|stat_desc
+
A1 |First Status
(1 row)
Following the example in the documentation, I try to set a psql
variable, but psql isn't interpreting it the way I expect it to.
>\set foo 'status'
> select * from :foo;
ERROR: parser: parse error at or near ":"
> \set foo status
> select * from :foo;
ERROR: parser: parse error at or near ":"
Any suggestions? Is the posted documentation out of date? How can
I use a psql variable? What is the alternative? What I really want
to do is use the 'psql --set' option so I can pass the path to the
plpgsql library from the command line.
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
:plpgsqlpath LANGUAGE 'C';
Any help is appreciated,
Sarah Officer
[EMAIL PROTECTED]
Re: [GENERAL] parser
I had this problem a couple years ago. I changed the column name before the upgrade by renaming the table to table_old, creating a new table with the original table name by selecting from table_old. For the column I needed to rename, I selected offset as 'x_offset'. I made sure everything was there, then dropped table_old and exported the database prior to the upgrade. It's a bit of trouble to find all the queries in the software and replace the column name, but for my application it was workable. Or maybe you already know how to do this but don't think you should have to? I hope this helps. Sarah Officer officers Jeff MacDonald wrote: > > hi, got this question from a user, anyone up for some discussion on it > > 1) I have been using 6.3 and recently tried to > move to 6.5. I found that the word "offset" is now a reserved word and I have used >it as a field name in > a table. 6.5 reports this as an error. I wonder if the parser should do this >since the context is as a > field name. > > == > Jeff MacDonald > [EMAIL PROTECTED] irc: bignose on EFnet > == > >
[GENERAL] Creating Triggers
Thanks to Ed Loehr and others on the group, I finally was able to create triggers in my database. This is a summary of what I learned in the process. For the most part, I didn't find this in the documentation. If anything here is incorrect, please let me know. If not, can it be put in documentation somewhere? or in the FAQ? - The actual working code for a trigger must be put into a function which is called by the trigger. [This *is* in the docs] - If the trigger function needs access to rows which are affected by the insert/update/delete, the trigger function must use plpgsql as a language. A sql function cannot access the special 'old' and 'new' rows. - Before creating a function in plpgsql, a handler and trusted language must be created. Example syntax: CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/install/lib/path/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; - The return type for a plpgsql function must be opaque. - A value must be returned if a return type is specified. The old & new records are available as return values from the plpgsql function. - The body of a plpgsql function looks like sql except for reference to old and new. The SQL part of the function must be enclosed with 'begin' and 'end;' or there will be a compiler error at run time. - Example triggers and plpgsql functions can be found in the postgres subdirectory: src/test/regress/sql. - If a trigger function is dropped and recreated, the corresponding trigger must also be dropped and recreated. Otherwise postgres 6.5.3 will give a runtime error that the cache lookup failed.
[GENERAL] Trigger problem
I am trying to create a simple trigger function. With some help
from the mailing list, I managed to create a trigger and functions.
Unfortunately I get an error message when I delete from the table
which has the trigger. Can anyone help me spot the error? Here's
what I have done:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/achilles_usr12/mars/swl/IRIX6.5/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
create table Images (
id varchar(100) PRIMARY KEY,
title varchar(25)NOT NULL,
filepath varchar(256) NOT NULL UNIQUE,
status_codevarchar(5) NOT NULL
) ;
create table Istatus (
status_codevarchar(5) PRIMARY KEY,
status_descvarchar(100) NOT NULL
);
CREATE FUNCTION remove_status_func()
RETURNS opaque AS '
delete from Images
where Images.status_code = old.status_code ;
select 1 as val;
' LANGUAGE 'plpgsql' ;
CREATE TRIGGER Istatus_delete_trigger
AFTER DELETE ON Istatus
FOR EACH ROW
EXECUTE PROCEDURE remove_status_func() ;
Insert into Istatus(status_code, status_desc)
values('A1', 'A1 Desc');
Insert into Istatus(status_code, status_desc)
values('A2', 'A2 Desc');
Insert into Istatus(status_code, status_desc)
values('A3', 'A3 Desc');
Insert into Images(id, title, filepath, status_code)
values ('ID1', 'First Image', '/usr/local/foo.gif', 'A1');
Insert into Images(id, title, filepath, status_code)
values ('ID2', 'Another Image', '/usr/local/bar.gif', 'A2');
> select * from istatus;
status_code|status_desc
---+---
A1 |A1 Desc
A2 |A2 Desc
A3 |A3 Desc
(3 rows)
> select * from images;
id |title|filepath |status_code
---+-+--+---
ID1|First Image |/usr/local/foo.gif|A1
ID2|Another Image|/usr/local/bar.gif|A2
(2 rows)
> delete from istatus where status_code = 'A1';
ERROR: fmgr_info: function 18848: cache lookup failed
What is the problem with the cache lookup? Any suggestions would be
appreciated.
Sarah Officer
[EMAIL PROTECTED]
Re: [GENERAL] psql problem describing tables
Let me clarify. The reason there is not data in my table is because I haven't inserted any yet. I inserted a row of data. It gets selected correctly, but I still can't describe the table. So the problem doesn't seem to be related to having an empty table. Sarah Sarah Officer wrote: > > I am running postgres 6.5.3 on an SGI. I haven't done much except > create a few tables and indexes. When I enter psql and type '\d', > all my tables and indexes are listed. When I type '\d tablename' > however, I get the following message: > > \d Images > ERROR: typeidTypeRelid: Invalid type - oid = 0 > > I can select from the table, but there's no data in it. I have the > same problem with all my tables, and I can't describe indexes > either. Any suggestions? > > Thanks, > > Sarah Officer > [EMAIL PROTECTED] > >
[GENERAL] psql problem describing tables
I am running postgres 6.5.3 on an SGI. I haven't done much except create a few tables and indexes. When I enter psql and type '\d', all my tables and indexes are listed. When I type '\d tablename' however, I get the following message: \d Images ERROR: typeidTypeRelid: Invalid type - oid = 0 I can select from the table, but there's no data in it. I have the same problem with all my tables, and I can't describe indexes either. Any suggestions? Thanks, Sarah Officer [EMAIL PROTECTED]
[GENERAL] triggers & functions
Hi, I'm porting a database from Oracle, and I'm having difficulty working out the syntax & logic for porting the triggers. Here's an example of what I have in Oracle: create table Images ( id varchar(100) PRIMARY KEY, title varchar(25)NOT NULL, filepath varchar(256) NOT NULL UNIQUE, status_codevarchar(5) NOT NULL ) ; create table Istatus ( status_codevarchar(5) PRIMARY KEY, status_descvarchar(100) NOT NULL ); When I delete a record in Istatus, I want to delete any records in Images that have the given status code. Okay, this is a rather crude example, but I think if I can do this, I can do the rest. In Oracle, I write the trigger something like this: CREATE TRIGGER istatus_delete_trigger AFTER DELETE ON Istatus FOR EACH ROW BEGIN delete from Images i where i.status_code = :old.status_code; END; Based on the documents and regression tests in the distribution, it looks like I need to move the meat of the trigger into a function for postgres. In postgres I'll call the procedure from the trigger. Well, after going through the docs & looking at examples, I haven't figured it out. My inclination is to write: CREATE FUNCTION remove_status_func() RETURNS int4 AS ' delete from Images where Images.status_code = old.status_code ; select 1 as val; ' LANGUAGE 'sql' ; I don't want to return anything, but that doesn't seem to be an option. Is opaque equivalent to no return value? I couldn't find it in the docs. Postgres gave me a message that opaque types weren't allowed if the language is sql. Why? So I have a dummy return value, but now Postgres doesn't seem to like the reference to 'old'. I see examples of functions which use 'old' in the plpgsql.sql regression set, but those specify a different language (even though that language looks like sql). I didn't find the definition of that language after poking around. Can anyone set me straight here? An example of a trigger which calls a sql procedure would be much appreciated! I'd like the function to be able to access the rows which are being removed. Thanks, Sarah Officer [EMAIL PROTECTED]
Re: [GENERAL] Simulating an outer join
Can somebody comment on using EXISTS vs. IN in a subselect? I have some statements with subselects, and I'd like to understand the ramifications of choosing EXISTS or IN. Sarah Officer [EMAIL PROTECTED] Mike Mascari wrote: > > Bruce Momjian wrote: > > > > I have been thinking about how to simulate an outer join. It seems the > > best way is to do: > > > > SELECT tab1.col1, tab2.col3 > > FROM tab1, tab2 > > WHERE tab1.col1 = tab2.col2 > > UNION ALL > > SELECT tab1.col1, NULL > > FROM tab1 > > WHERE tab1.col1 NOT IN (SELECT tab2.col2 FROM tab2) > > > > Comments? I know someone was asking about this recently. > > > > I wouldn't use IN ;-) > > SELECT table1.key, table2.value > FROM table1, table2 > WHERE table1.key = table2.key > UNION ALL > SELECT table1.key, NULL > FROM table1 WHERE NOT EXISTS > (SELECT table2.key FROM table2 WHERE table1.key = table2.key); > > Mike Mascari > >
[GENERAL] views containing agregates questions
In the docs directory of the 6.5.3 distribution, I was browsing through the TODO file. There is a note there: 'Views containing aggregates sometimes fail(Jan)' In what way do they fail? I need to create several views with aggregates, and I'm concerned about what the effect will be. I tried to search the archives on the postgres page, but when I enter a keyword such as 'view', I get the message "Can't open template file 'views '!". Any information on the views/aggregate functions problem would be appreciated. Sarah Officer [EMAIL PROTECTED]
[GENERAL] joins between databases
Can I do a select joining tables of one database with tables in another database? Both databases would be managed by the same postmaster. Thanks, Sarah Officer [EMAIL PROTECTED]
[GENERAL] index on an int8 column
I have a table with a column of type int8. When I try to create an index on it, the database protests and gives the following error message: ERROR: Can't find a default operator class for type 20. Is there an easy fix for this? I assumed builtin numeric types would have default comparison functions. Also, if I am not posting to the appropriate mailing list, please let me know. Thanks in advance, Sarah Officer [EMAIL PROTECTED]
[GENERAL] entity relationship diagram free software
I am looking for a freeware tool to document my postgres database tables, indices, etc. A friend of mine uses ERwin, but this is a licensed package his company had to buy. I Are there any free software tools that people use to create entity relationship diagrams and to document databases? Thanks, Sarah Officer [EMAIL PROTECTED]
