Re: [GENERAL] return two elements
You could use your own type, since it seems you know what values you'll be storing there. You can create it like this: CREATE TYPE MyResult AS ( dt_inici DATE, dt_fi DATE ); Then you must change your functions as follows: CREATE OR REPLACE FUNCTION test1() RETURNS MyResult AS ' declare r MyResult; begin r.dt_inici:=\'01/01/2005\'::Date; r.dt_fi:=\'02/02/2005\'::Date; RETURN r; end; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION test2() RETURNS date AS ' declare r MyResult; begin r:=test1(); RETURN r.dt_inici; end; ' LANGUAGE 'plpgsql' VOLATILE; To me, using a TYPE seems clearer than using an array in this case. 2005/6/6, Rodríguez Rodríguez, Pere [EMAIL PROTECTED]: Hello, how can I write a function that return two or more elements? I try to return a record but after I don't know how to assign returned value to a variable. For example, CREATE OR REPLACE FUNCTION test1() RETURNS record AS ' declare rec record; begin select into rec \'01/01/2005\'::Date as dt_inici, \'02/02/2005\'::Date as dt_fi; return rec; end; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION test2() RETURNS date AS ' declare rec record; begin -- rec := test1(); ERROR: syntax error at or near rec select into rec test1(); return rec.dt_inici; ERROR: record rec has no field dt_inici end; ' LANGUAGE 'plpgsql' VOLATILE; Can I return an array?, and how I take returned array? I search postgres documentation but I don't find it Thanks in advance, pere
Re: [GENERAL] return two elements
You could use your own type, since it seems you know what values you'll be storing there. You can create it like this: CREATE TYPE MyResult AS ( dt_inici DATE, dt_fi DATE ); Then you must change your functions as follows: CREATE OR REPLACE FUNCTION test1() RETURNS MyResult AS ' declare r MyResult; begin r.dt_inici:=\'01/01/2005\'::Date; r.dt_fi:=\'02/02/2005\'::Date; RETURN r; end; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION test2() RETURNS date AS ' declare r MyResult; begin r:=test1(); RETURN r.dt_inici; end; ' LANGUAGE 'plpgsql' VOLATILE; To me, using a TYPE seems clearer than using an array in this case. 2005/6/6, Rodríguez Rodríguez, Pere [EMAIL PROTECTED]: Hello, how can I write a function that return two or more elements? I try to return a record but after I don't know how to assign returned value to a variable. For example, CREATE OR REPLACE FUNCTION test1() RETURNS record AS ' declare rec record; begin select into rec \'01/01/2005\'::Date as dt_inici, \'02/02/2005\'::Date as dt_fi; return rec; end; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION test2() RETURNS date AS ' declare rec record; begin -- rec := test1(); ERROR: syntax error at or near rec select into rec test1(); return rec.dt_inici; ERROR: record rec has no field dt_inici end; ' LANGUAGE 'plpgsql' VOLATILE; Can I return an array?, and how I take returned array? I search postgres documentation but I don't find it Thanks in advance, pere
Re: [GENERAL] starting postgresql with pgsql password - workarounds?
This is not a PostgreSQL problem, it's the script you are using for startup that has some problem. The pg_hba method is for connection stablishment. PostgreSQL will start no matter what you put there. Startup scripts are usually run as root, and postgresql script should su to the postgresql user to start the database. I don't know what your script is doing, but root should be able to su to any user without password. Check your script, post it if you want. It would be usefull to know what system you are using also (linux/bsd/solaris/etc). 2005/5/20, Duane Winner [EMAIL PROTECTED]: hello,I've been using postgresql for about a year now, and am prettycomfortable with the basics, bu there has been something bugging me fora while now:I set the METHOD in pg_hba.conf to md5 so that a password is required from all users, from all hosts.The only problem is that if the server restarts, postgresql will notstart until somebody goes to the console and enters the password for thepgsql account.Is there a solution for this solution? Thanks,DW---(end of broadcast)---TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] starting postgresql with pgsql password - workarounds?
mmmhhh, I have never installed postgresql from the ports. I don´t know
what the script is doing, probably it´s checking that Postgresql
directory is initialized.
Anyway, here is my homemade script, you could replace yours with it (check it first, but it´s quite simple).
My script does not tell postgresql to listen on tcp sockets, but you can enable it in your postgresql.conf.
#!/bin/sh
case $1 in
start)
echo -n postgresql
su -l pgsql -c '~/bin/pg_ctl start'
;;
stop)
echo -n postgresql
su -l pgsql -c '~/bin/pg_ctl stop -mf'
;;
restart)
echo -n reloading postgresql
echo stopping
$0 stop
echo
echo starting
$0 start
echo
;;
reload)
echo reloading postgreSQL
su -l pgsql -c '~/bin/pg_ctl reload -mf'
echo
;;
status)
echo -n postgresql
su -l pgsql -c '~/bin/pg_ctl status'
;;
*)
echo
echo Use: $0 [ start | stop | restart | reload | status ]
echo
;;
esac
exit 0
Hope it helps.
2005/5/20, Duane Winner [EMAIL PROTECTED]:
I am using the default startup script that is supplied with the FreeBSDport (/usr/local/etc/rc.d/010.pgsql.sh) and enabling it in /etc/rc.dwith -o -i flags so listens on TCP/IPAlso, I should mention that the password I mentioned is NOT the password
for the local (Unix) pgsql account, but the password I set for thepostgresql database superuser, pgsql. That is the password I need toenter to get postgresql to start.Thanks,DW-
#!/bin/sh# $FreeBSD: ports/databases/postgresql74-server/files/pgsql.sh.tmpl,v1.17 2005/03/19 03:51:45 girgen Exp $## PROVIDE: postgresql# REQUIRE: LOGIN# KEYWORD: FreeBSD shutdown#
# Add the following line to /etc/rc.conf to enable PostgreSQL:##postgresql_enable=YES## optional#postgresql_data=/usr/local/pgsql/data#postgresql_flags=-w -s -m fast
## This scripts takes one of the following commands:## start stop restart reload status initdb## For postmaster startup options, edit ${postgresql_data}/postgresql.confprefix=/usr/local
. /etc/rc.subrload_rc_config postgresql# set defaultspostgresql_enable=${postgresql_enable:-NO}postgresql_flags=${postgresql_flags:--w -s -m fast}postgresql_user=pgsql
eval postgresql_data=${postgresql_data:-~${postgresql_user}/data}postgresql_class=${postgresql_class:-default}name=postgresqlrcvar=`set_rcvar`command=${prefix}/bin/pg_ctl
command_args=-D ${postgresql_data} ${postgresql_flags} $1extra_commands=reload initdbstart_cmd=postgresql_command startstop_cmd=postgresql_command stoprestart_cmd=postgresql_command restart
reload_cmd=postgresql_command reloadstatus_cmd=postgresql_command statusinitdb_cmd=postgresql_initdbpostgresql_command(){su -l ${postgresql_user} -c exec ${command} ${command_args}
}postgresql_initdb(){su -l -c ${postgresql_class} ${postgresql_user} -c exec${prefix}/bin/initdb -D ${postgresql_data}}run_rc_command $1-
Franco Bruno Borghesi wrote: This is not a PostgreSQL problem, it's the script you are using for startup that has some problem. The pg_hba method is for connection stablishment. PostgreSQL will start no matter what you put there.
Startup scripts are usually run as root, and postgresql script should su to the postgresql user to start the database. I don't know what your script is doing, but root should be able to su to any user
without password. Check your script, post it if you want. It would be usefull to know what system you are using also (linux/bsd/solaris/etc). 2005/5/20, Duane Winner
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: hello, I've been using postgresql for about a year now, and am pretty
comfortable with the basics, bu there has been something bugging me for a while now: I set the METHOD in pg_hba.conf to md5 so that a password is required from all users, from all hosts.
The only problem is that if the server restarts, postgresql will not start until somebody goes to the console and enters the password for the pgsql account.
Is there a solution for this solution? Thanks, DW ---(end of broadcast)--- TIP 6: Have you searched our list archives?
http://archives.postgresql.org http://archives.postgresql.org
---(end of broadcast)---TIP 3: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to
[EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly
Re: [GENERAL] Ayuda con postgresql
Si necesitás una lista en español, podés ir a http://archives.postgresql.org/pgsql-es-ayuda y suscribirte, los suscriptores de esta lista hablan inglés y no te va a resultar fácil conseguir ayuda :( El tipo de datos que buscas se llama SERIAL (http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL) La utilidad para generar un vuelco de la base de datos es pg_dump (http://www.postgresql.org/docs/8.0/interactive/backup.html#BACKUP-DUMP) y el proceso inverso lo podes realizar directamente con el psql como se explica en http://www.postgresql.org/docs/8.0/interactive/backup.html#BACKUP-DUMP-RESTORE. La documentacion de la base de datos está en http://www.postgresql.org/docs/8.0/interactive/index.html. Espero te sirva. El día 19/05/05, Maribel Pérez Engroñatt [EMAIL PROTECTED] escribió: Hola! buenos días! Estoy usando su base de datos y quisiera saber como creo un campo que sea auntonumérico o lo que en sql server es identiti, también quisiera como pudiera hacer una copia de toda mi base de datos (restore) para copiarla igual en otra computadora. Saludos Maribel Correo Yahoo!Comprueba qué es nuevo, aquíhttp://correo.yahoo.es
Re: [GENERAL] Disabling Triggers
You could add a TIMESTAMP field on the three tables (lets call it last_change), and modify your triggers to update this value every time a row is updated. Then your trigger should update the boolean fields with the boolean value of the row with the max(last_change) in the three tables, only if the row of the table the trigger is being fired for is less than this max(last_change) value. Hope this is understandable :) Of course you could remove the boolean value from the three tables, create another table with the boolean value, and forget about the triggers. But I'm sure you have already though that. Hope it helps. 2005/5/11, Mark Borins [EMAIL PROTECTED]: I am creating a system where I have a trigger on three different tables. There is a particular Boolean field in each of these tables that when it is set in table it should be set the same in the other two. So I figured I could put a trigger on each table that when the Boolean field was updated it would go and update the other 2. However, I am concerned about cascading trigger calls. Does anyone know if it is possible to run an update statement on a table and for only that statement disable the trigger on the table?
Re: [GENERAL] sequence values question
just obtain the next value from the sequence first, then do the insert:
CREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsql
AS $$
DECLARE
vdesc alias for $1;
new_id INTEGER;
BEGIN
SELECT nextval('sequence_name_here') INTO new_id;
INSERT INTO productos (id, desc) VALUES (new_id, vdesc);
RETURN (new_id) || ',' || vdesc;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
2005/5/10, [EMAIL PROTECTED] [EMAIL PROTECTED]:
Hi, how can i know the values generated by a column of type serial?I mean, i have the following tableproductos(id serial,desc varchar(50))select * from productos;+-++
| id| desc |+-++| 1 | ecard1 || 2 | ecard2 || 3 | ecard3 || 4 | ecard4 || 5 | ecard5 |+-++I insert a row using a SP, i want to return the id and desc of the new
product in the table.this is an example of the hypothetical SPCREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsqlAS $$DECLAREvdesc alias for $1;BEGININSERT INTO productos (desc) VALUES (vdesc);
RETURN (new id ???) || ',' || vdesc;END;$$LANGUAGE 'plpgsql' VOLATILE;I know i can get the last value using currval(text), and add 1 to the nextvalues, is this the only way?, what if i want to insert several products?,
should ireturn a record ?thanks---(end of broadcast)---TIP 8: explain analyze is your friend
Re: [GENERAL] Howto prevent write based on date
You could write a trigger like this: CREATE OR REPLACE FUNCTION checkDate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS ' DECLARE limitDate DATE DEFAULT current_date-''1 year''::INTERVAL; BEGIN IF (OLD.date=limitDate) THEN RAISE EXCEPTION ''Cannot change record.''; END IF; RETURN NEW; END; '; CREATE TRIGGER _tg1 BEFORE UPDATE OR DELETE ON FOR EACH ROW EXECUTE PROCEDURE checkDate(); This should do the job :) 2005/5/4, Fmiser [EMAIL PROTECTED]: I'm a newbie to database admin, but I'm not afraid to try - but this onehas me stumped.I'm using SQL-Ledger as a front end to postgresql 7.4.2. on (mostly)Debian Testing, i386.My fiscal year is over and I would _like_ to prevent any changes to the data from last year.I looked/searched in the manual, but I don't even know what to call whatit is that I'm trying to do!lock has another meaning for databases. :)Write seems to bring up lots of user-related stuff. So, I'm hoping one of you geniuses can tell me where to look, what tolook for, or how to do it. *smile*Thanks! Philip, wanabe-admin---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Date addition/subtraction
It's easy. You have to know that INTERVAL data type exist, so: SELECT current_date - '30 days'::interval SELECT current_timestamp - '1 hour'::interval 2005/5/3, Craig Bryden [EMAIL PROTECTED]: HiHow in postgres can I do date/time subtraction or addition.e.g. If I want to get today's date - 30 days? or current_timestamp - 1 hour?ThanksCraig---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly
Re: [GENERAL] Sorting by constant values
You can order by conditions, lets say column='Unit'. The evaluation of a conditions will give you 't' or 'f', and alfabetically 'f' 't'... you should use DESC to get the matches first. So, it would be more or less like this: ORDER BY column='Unit' DESC, column='Exterior' DESC, column='Common' DESC I don't think this is performant though. If you have many rows to evaluate, you could create a funtion like this: CREATE FUNCTION evaluate(TEXT) RETURNS TEXT LANGUAGE 'sql' AS ' SELECT $1='Unit' || $1='Exterior' || $1='Common'; ' This function would return something like 'tff', 'ftf', 'fft', and you should be able to create an index on that function. Then you can use the index to order your rows. Hope it helps ;) 2005/5/3, Robert Fitzpatrick [EMAIL PROTECTED]: I have a column that I want to sort by certain values. The values areUnit, Exterior and Common. I want all the records with Unit first,Common second and Exterior last in the sort order. These are the only 3possible values, is there a way to sort manually like that with the alphanumeric values?--Robert---(end of broadcast)---TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] sub-query question
something == otherthing is a boolean expression, you are asking the
database to compare both values, u.color_id is not equal c.color_name,
that's why you get 'f'.
I guess that you want to replace the color_id from users by the
corresponding color_name from colors:
SELECT
c.color_name, u.name, u.the_date
FROM
users u
INNER JOIN colors c ON (u.color_id=c.color_id)
WHERE
u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;
If you were trying to do another thing, I'm sorry, I didn't get it :(
Scott Frankel wrote:
How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?
i.e.: given the following two tables, I want to replace the color_id
of 1
with the color_name 'red.' (The SQL to create the two tables follows
below.)
test=# SELECT * from users ;
color_id | name | the_date
--+--+
1 | john | 2004-03-10
3 | jane | 2004-04-12
1 | joe | 2004-05-14
2 | jepe | 2004-06-16
(4 rows)
test=# SELECT * from colors;
color_id | color_name
--+
1 | red
2 | green
3 | blue
(3 rows)
My attempts yield an 'f' which looks suspiciously like a boolean false.
Is there an ordering issue with my sub-query, such that the sub-query
doesn't have enough info to perform its lookup?
Here's my query:
SELECT (
u.color_id = (
SELECT c.color_name
FROM colors c
WHERE color_id = 1)) AS color_name,
u.name, u.the_date
FROM users u
WHERE u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;
It returns:
color_name | name | the_date
+--+
f | joe | 2004-05-14
(1 row)
Thanks!
Scott
Here's the SQL to create my test tables:
CREATE TABLE colors (color_id SERIAL PRIMARY KEY, color_name text);
CREATE TABLE users (color_id integer REFERENCES colors, name text,
the_date date);
INSERT INTO colors (color_name) VALUES ('red');
INSERT INTO colors (color_name) VALUES ('green');
INSERT INTO colors (color_name) VALUES ('blue');
INSERT INTO users (color_id, name, the_date) VALUES (1, 'john',
'2004-03-10');
INSERT INTO users (color_id, name, the_date) VALUES (3, 'jane',
'2004-04-12');
INSERT INTO users (color_id, name, the_date) VALUES (1, 'joe',
'2004-05-14');
INSERT INTO users (color_id, name, the_date) VALUES (2, 'jepe',
'2004-06-16');
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Last value inserted
I think the best way would be not to use a SERIAL field, but an INTEGER
field and a sequence:
CREATE SEQUENCE parent_seq;
CREATE TABLE parent(id INTEGER, descrip CHAR(50));
So when you want to insert on the parent table, you obtain the next
value from the sequence and then you insert in the parent and child
tables the value you obtained:
newId:=SELECT nextval('parent_seq')
INSERT INTO parent(id, descrip) VALUES (newId, '');
INSERT INTO child_1(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_2(..., ..., parentId) VALUES (..., ..., newId);
INSERT INTO child_3(..., ..., parentId) VALUES (..., ..., newId);
hope it helps.
MaRCeLO PeReiRA wrote:
Hi guys,
I am in troubles with a SERIAL field.
I have five tables. A parent table and four child
tables. When I do the INSERT in the parent table, I
have an ID (generated) by the sequence (SERIAL field),
and I have to use this ID to reference all child
tables.
Well, once I do an INSERT in the parent table, how can
I know (for sure) which number id was generated by the
sequence?
Simple example:
--
CREATE TABLE parent(id SERIAL, descrip CHAR(50));
--
So,
--
INSERT INTO parent (descrip) VALUES ('project 1');
--
How can I now (for sure) with value was generated by
the sequence to fill the field ID?
(There is lots of users using the software at the same
time, so I am not able to use the last_value()
function on the sequence.)
Best Regards,
Marcelo Pereira
Brazil
___
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Mass Import/Generate PKs
the simplest way to do it seems to be adding a SERIAL column to your table, and then adding a primary key constraint: 1)insert data into table 2)ALTER TABLE table ADD id SERIAL; 3)ALTER TABLE table ADD CONSTRAINT table_pk PRIMARY KEY (id); you can check the docs for the SERIAL type: http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL On Sat, 2004-11-06 at 17:29, Hunter Hillegas wrote: That sounds nice and easy... So, I would do something like 'ALTER TABLE' to generate the PK column? What would be the best way to populate it? Is there an area of doco I should be looking at? Thanks, Hunter From: Peter Eisentraut [EMAIL PROTECTED] Date: Sat, 6 Nov 2004 21:21:25 +0100 To: Hunter Hillegas [EMAIL PROTECTED], PostgreSQL [EMAIL PROTECTED] Subject: Re: [GENERAL] Mass Import/Generate PKs Hunter Hillegas wrote: I have a CSV file with 400,000 lines of email mailing list information that I need to migrate to a new PostgreSQL database. Each line has all the info I need except a PK (I usually use an int4 column for a PK). You could import the file into PostgreSQL and add a primary key column later. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Mass Import/Generate PKs
I've tested it, and the SERIAL type populates the column when you add it
On Sat, 2004-11-06 at 18:56, Ed L. wrote:
On Saturday November 6 2004 2:13, Franco Bruno Borghesi wrote:
the simplest way to do it seems to be adding a SERIAL column to your
table, and then adding a primary key constraint:
1)insert data into table
2)ALTER TABLE table ADD id SERIAL;
3)ALTER TABLE table ADD CONSTRAINT table_pk PRIMARY KEY (id);
You may also need to populate the id column with unique values in between
these two steps with something like
update table set id = nextval('table_id_seq'::text) where id isnull
I don't think SERIAL does that for you.
Ed
attachment: smiley-4.png
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] Number of months
You could create your own function for the conversion, something like:
CREATE OR REPLACE FUNCTION interval2Months(INTERVAL) RETURNS INTEGER LANGUAGE 'sql' IMMUTABLE AS '
SELECT CAST(extract(YEAR FROM $1) * 12 + extract (MONTH FROM $1) AS INTEGER);
';
you call it doing SELECT interval2Months(age('2003-01-01'::date));
Of course, you can create a function that obtains the age directly from a DATE parameter and then converts that value to months.
Hope it helps.
On Wed, 2004-11-03 at 15:52, Patrick Hatcher wrote:
Is there a function that will give me the number of months, as an integer, in Pg 7.4.x? I found the date_trunc function but that will return text and I didn't see anything else?
I have this, but didn't want to duplicate the work if it wasn't necessary:
(date_part('Year', CURRENT_DATE) -date_part('Year', SOMEDATE)) * 12 +date_part('Month', CURRENT_DATE)-date_part('Month',SOMEDATE)
TIA
Patrick Hatcher
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] ON DELETE trigger blocks delete from my table
I've made a test case, and setting the trigger BEFORE DELETE doesn't delete the rows from the table (but it does execute the trigger, and it does insert the rows in the audit table), I dont' know why . Anyway, setting the trigger AFTER DELETE works ok. On Mon, 2004-10-25 at 15:56, Naeem Bari wrote: Hi, I am using postgres 7.4.5 on Redhat Enterprise Linux 3. My background is really on Oracle, and I am porting a largish database over to postgres. Here is my problem: On oracle, I had a table with an on update or delete trigger that copied the current row out to an audit table. Works like a champ. On postgres, when I try to delete a row, all it gives back to me is DELETE 0 and does nothing. Here is the text of the trigger: ~~ CREATE OR REPLACE FUNCTION public.func_job_status_upd() RETURNS trigger AS ' begin insert into x_job_status values ( OLD.job_id, OLD.job_status_type_id, OLD.status_date, OLD.notes, OLD.edit_person_id, OLD.edit_date); return new; end; ' LANGUAGE 'plpgsql' VOLATILE; ~~ Any help would be appreciated! Thanks, naeem attachment: smiley-6.png signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Postgres update with self join
This is the way you do it in postgreSQL:
UPDATE
test
SET
code=T2.code
FROM
test T2
WHERE
test.code=0 AND
test.master=T2.name;
you need to specify the join condition in the WHERE clause.
On Tue, 2004-08-10 at 00:34, Igor Kryltsov wrote:
Hi,
If you can help me to correct my mistake.
To simplify my question:
I have table:
create table test (
name varchar(10),
code integer,
master varchar(10));
I have values:
insert into test values ('ABC', 15074, null);
insert into test values ('ABC1', 0, 'ABC');
insert into test values ('ABC2', 0, 'ABC');
insert into test values ('EKL', 15075, null);
insert into test values ('EKL1', 0, 'EKL');
Table looks like:
select * from test;
name | code | master
--+---+
ABC | 15074 |
ABC1 | 0 | ABC
ABC2 | 0 | ABC
EKL | 15075 |
EKL1 | 0 | EKL
(5 rows)
Now I need to replace 0 values in code column by corresponding code
values by following link between master field in a record where code=0 and
name field where it is not.
By the other words first two 0's have to be replaced with 15074 and last 0
with 15075.
This update works in MSSQL but in Postgres it replaces code values as shown
below.
update test
set code = i1.code
from test i1
join test i2 on i1.name = i2.master
where i2.code = 0;
select * from test;
name | code | master
--+---+
ABC | 15074 |
ABC1 | 15074 | ABC
ABC2 | 15074 | ABC
EKL | 15074 |
EKL1 | 15074 | EKL
(5 rows)
... all values 15074.
Thank you,
Igor
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] enumerated type..
CREATE DOMAIN is the answer: CREATE DOMAIN mytype AS INT2 CHECK(VALUE=1 AND VALUE=6); On Mon, 2004-07-19 at 10:43, [EMAIL PROTECTED] wrote: hi, Is it possible in Postgre to have enum type, so that later table fileld get values from 1-6 (saving space) 1 - statex 2 - stateY 3 - stateZ something like that and then constrain on this enum type.. tia ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Error in postgreSQL
you should check the logs, when postmaster dies right after starting it always leaves a helpfull message there. On Wed, 2004-07-07 at 10:09, Jos Augusto Tovar wrote: Hi all, Im using postgreSQL in WinME and i had a very strange problem. When i start the postgres the process postmaster starts, but it stops soon after. So i cant use the bd. Does someone know the problem? Tks __ Jos Augusto Tovar - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly signature.asc Description: This is a digitally signed message part
Re: [GENERAL] newbie question... how do I get table structure?
this should work (don't forget to replace TABLE NAME!!!): SELECT A.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) AS typeName FROM pg_class C, pg_attribute A, pg_type T WHERE C.relname ILIKE 'TABLE NAME' AND (C.oid=A.attrelid) AND (T.oid=A.atttypid) AND (A.attnum0) AND (NOT A.attisdropped) ORDER BY A.attnum; Does anyone know if the ansi sql standard defines any way to do this? I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands in other databases, but I don't really know if they are extensions or not. On Fri, 2004-02-06 at 11:10, Aaron Bratcher wrote: Is there no way I can do it with a standard select command in a different client? I don't need the indexes, just the column names/types. -- Aaron Bratcher ab DataTools http://www.abDataTools.com On Feb 6, 2004, at 8:12 AM, Harald Fuchs wrote: In article [EMAIL PROTECTED], Joshua D. Drake [EMAIL PROTECTED] writes: Aaron Bratcher wrote: What command can I use to get the structure of a given table? If psql is client \d tablename Without psql you can use pg_dump -s DBNAME -t TBLNAME from your shell prompt. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings signature.asc Description: This is a digitally signed message part
Re: [GENERAL] max_fsm_pages
I've read this message, and tryed to apply this changes to my own database. sapiens=# vacuum verbose; ... INFO: free space map: 1 relations, 39 pages stored; 48 total pages needed DETAIL: Allocated FSM size: 1000 relations + 3 pages = 237 kB shared memory. 1 relation and 48 pages seems too little, considering the defaults in the configuration. The database is not too big, just 198 mb (contrib/dbsize tells me so), and I have 161 relations on pg_class (not counting the catalog). I'm doing something wrong or 1 relation/48 pages would be just fine? Thanks. On Fri, 2003-12-05 at 13:17, Tom Lane wrote: Ryan Mahoney [EMAIL PROTECTED] writes: When interactively calculating the ideal value for max_fsm_pages by summarizing the output of VACUUM VERBOSE, which statistic from vacuum am I concerned with? 7.4 will tell you exactly how many FSM slots you need: foo=# vacuum verbose; ... INFO: free space map: 246 relations, 464 pages stored; 4160 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. VACUUM In this example, the minimum FSM settings to not discard any data would be max_fsm_relations = 246, max_fsm_pages = 4160. Note that you need to have vacuumed all databases fairly recently for the totals to be really trustworthy. In previous versions you're kind of on your own :-( regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly signature.asc Description: This is a digitally signed message part
Re: [GENERAL] pg_clog problems
The file was there, but maybe some blocks where missed (it's a fat32 fs). Finally I reinstalled the database and the problem got solved, but now I know what to do for the next time Thanks Martijn. On Wed, 2003-11-19 at 21:31, Martijn van Oosterhout wrote: On Wed, Nov 19, 2003 at 01:45:21PM -0300, Franco Bruno Borghesi wrote: Hi everyone. I'm having problems with a database, I get the following errors while accesing it (from my application and from pg_dump) ERROR: Could not access status of transaction 65536 DETAIL: Could not read from file /usr/share/postgresql/data/pg_clog/ at offset 16384: No error. I'm using postgreSQL 7.4RC2 under cygwin (on winxp). It probably got damagged because of a bad windows shutdown or something. My question is, is there a way to reinit pg_clog files? I had read somewhere about something called pgsql_fsck or something, would this tool fix the problem? (if yes, please tell me the correct name ;) ) Simply create the clog file as 256K of zeros. If it's missing you need to create it. But you really shouldn't be missing it since it's the first one created. You didn't delete anything by accident? Hpoe this helps, attachment: smiley-10.png signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Is it possible find a size of a table
you need to install contrib/dbsize. Then you can do:
SELECT relation_size('myTable');
On Thu, 2003-11-20 at 02:30, K. Deepa wrote:
Hi All,
Is it possible find the size of a pgsql table.
TIA
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] pg_clog problems
don't worry!! it's just a cygwin installation for a demostration application for the sales team. Real application runs on bsd On Thu, 2003-11-20 at 11:23, Alvaro Herrera Munoz wrote: On Thu, Nov 20, 2003 at 10:48:08AM -0300, Franco Bruno Borghesi wrote: The file was there, but maybe some blocks where missed (it's a fat32 fs). Finally I reinstalled the database and the problem got solved, but now I know what to do for the next time :P Doh ... Don't run your database in a FAT filesystem if you value your data! It's probably as unreliable as it can get, and slow too. attachment: smiley-4.png signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Point-in-time data recovery - v.7.4
I think it was delayed until 7.5... same for win32 port. Here ir Bruce's message talkin about both topics: http://archives.postgresql.org/pgsql-hackers/2003-07/msg00284.php On Tue, 2003-11-18 at 11:01, Rafael Martinez Guerrero wrote: Hello I am trying to find out when 'Point-in-time data recovery' functionality will be available with postgreSQL but I can not find concrete info about this. References in mailinglists talk about version 7.4 and in the TODO list is under the section 'urgent'. Anybody knows when this functionality will be available with prod-quality? signature.asc Description: This is a digitally signed message part
Re: [GENERAL] mysql create table - psql
the problem is
constraint id PRIMARY (id), it should be constraint id PRIMARY KEY (id).
Besides that, you will have problems with '-00-00'... month starts at 1, day starts at 1, and I don't know about year 0. Maybe this field 'time' should be NULLable, or maybe its default value should be '0001-01-01 00:00:00'.
On Tue, 2003-09-09 at 03:10, expect wrote:
Hello,
Trying to get this MySql create table command to work, no luck.
create sequence serial;
CREATE TABLE outbound (
source char(100) default '',
destination char(100) default '',
sport int4 default 0 NOT NULL,
dport int4 NOT NULL default 0,
time timestamp NOT NULL default '-00-00 00:00:00',
id int8 default nextval('serial') not null,
constraint id PRIMARY (id)
);
I get a parse error:
ERROR: parser: parse error at or near ( at character 279
---(end of broadcast)---
TIP 8: explain analyze is your friend
signature.asc
Description: This is a digitally signed message part
[GENERAL] SRF question
Hi everyone. I need to create a C function that returns a row, and the type of this row must be the same of a type that I've created using CREATE TYPE. I've seen the function: TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases) but I don't understand two things: 1)I just know the name of the type, not its oid... woud it be ok if I execute a SELECT on pg_type asking for typname to find the oid of the type I want to return? or is there any function like RelationNameGetTupleDesc (maybe TypeNameGetTupleDesc) that I can call passing just the typename as a char *? 2)in case TypeGetTupleDesc is the only option, what is the second argument? Thanks in advance. PS: sorry if you receive this message twice, I sent it on saturday but it seems it never arrived :) signature.asc Description: This is a digitally signed message part
Re: [GENERAL] SRF question
yes, parseTypeString() seems to be the way.
I've found parseTypeString() in plpgsql.so... would it be ok to call dlopen('plpgsql.so', ...) from my C code to access this function or is there some other method I sould use?
thanks tom.
On Mon, 2003-09-08 at 12:02, Tom Lane wrote:
Franco Bruno Borghesi [EMAIL PROTECTED] writes:
1)I just know the name of the type, not its oid... woud it be ok if I
execute a SELECT on pg_type asking for typname to find the oid of the
type I want to return? or is there any function like
RelationNameGetTupleDesc (maybe TypeNameGetTupleDesc) that I can call
passing just the typename as a char *?
A SELECT seems like the hard way. Look into parse_type.c for some
routines that might help. Offhand parseTypeString() is probably your
best bet.
2)in case TypeGetTupleDesc is the only option, what is the second
argument?
I believe you can just set it to NIL, since you are only interested in
composite types.
regards, tom lane
signature.asc
Description: This is a digitally signed message part
[GENERAL] C functions
Hi guys! I need to migrate some plpgsql functions I have to C language. These functions execute queries and process the results inside, and some of them return records... I've been reading the documentation, but I would like any of you to tell me where can I find some examples of these kind of functions (in C or C++). Thanks in advance. signature.asc Description: This is a digitally signed message part
Re: [GENERAL] table constraints and performance
Besides the performance issues, I think it's correct to detect refreshes before sending data to the database. In our applications, we ignore refreshes from the begining. We do it by sending a serial number, which we keep on a session variable. Every time we send a page to a client, we increment this number, update the session variable, and send it as a hidden within the page. When the user submits the page (and the hidden value), we check the serial number submited is equal to the session's serial number. If it is, we write to the database, if it's not, we just skip the writing code. This way, when the user refreshes the page, the session variable always gets incremented at the server, but the value submited is always the same. You can implement it in an easy generic way, and you don't waste time sending updates to the database that you know will fail anyway. Hope it helps :) On Mon, 2003-08-25 at 14:44, Brian Maguire wrote: Could someone provide me with some information in regards to the performance implications of applying constraints on a table? Are there any safe guards? The basic scenario is that there is a table that has 80% updates and inserts and 20% selects. I would like to restrict duplicate inserts from user double clicking or other user behavior such as refresh. I am after advice on what the performance implications would be on placing restraints on a table vs. writing business logic code. Thanks, Brian ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Buglist
Initial beta release of plPHP http://www.postgresql.org/news/143.html On Tue, 2003-08-19 at 10:46, David Siebert wrote: I learned MySQL then went on to Postgres. I chose postgres for my in house project just because of the row locking and transactions. Looking back I could have used MySQL. I have yet to use stored procedures or many of the high level functions of Postgres however transactions make things so much cleaner. I do not think MySQL is a bad system. It works well for many people in many situations. I think that MySQL and SAP getting together could be very exciting. When it comes to SQL databases I would say we have a wealth good choices. This if I use PHP I have to use MySQL is a load of tripe. PHP can work just fine with Postgres. I hate to even suggest this but has anyone thought of adding PHP to the languages that you can use to write stored procedures in Postgres? Roderick A. Anderson wrote: On 19 Aug 2003, Bo Lorentsen wrote: Also have anyone tryed to compare the new transaction model in MySQL 4.x to PostgreSQL ? Bo, I've recently started having to deal with MySQL. (Web sites wanting/using php _need/have-to-have_ MySQL. Their words not mine.) And from going from a I dislike MySQL to I'm really hating MySQL has been getting easier and easier. My dealings with MySQL are for the 3.xx version but I semi-followed a thread on this several months ago so feel fully qualified to to throw in my views. :-) My take on others research was that MySQL transaction model is a bubble gum and bailing wire add on not an integral part of MySQL. It _was_ tacked onto the top of the database so if either it or MySQL failed you were likely to loose data. I'm looking forward to recive even more constructive arguements :-) How about Friends don't let friends use MySQL? Hopefully others with a stonger knowledge will provide this. Rod ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly signature.asc Description: This is a digitally signed message part
Re: [GENERAL] How to do?
mmm... I don't understand. The query brings a resultset just like the one you asked. When you say that 'there's no guarantee that A field is sorted or unique...', what do you mean? The query doesn't care about the A field, it just needs UID to be a candidate key. And I still don't understand what you need the row number for... On Tue, 2003-08-05 at 09:04, Robert Partyka wrote: At 20:05 03-08-01 -0300, you wrote: This is the best I could come up with: SELECT F1.a, F1.b, F1.uid FROM foo F1 LEFT JOIN ( SELECT uid FROM foo WHERE a=(SELECT a FROM foo WHERE uid='AC88') AND uid'AC88' ORDER BY a LIMIT 1 ) F2 ON (F2.uid=F1.uid) LEFT JOIN ( SELECT uid FROM foo WHERE a=(SELECT a FROM foo WHERE uid='AC88') AND uid'AC88' ORDER BY a LIMIT 1 ) F3 ON (F3.uid=F1.uid) WHERE F1.uid='AC88' OR F2.uid IS NOT NULL OR F3.uid IS NOT NULL I don't know how this query perfroms, but I'm sure it works :) Explained: -F2 has the first record *after* AC88. -F3 has the first record *before* AC88 -The condition (the main WHERE) asks for the AC88 record itsself, or any record where uid is not null (which are the ones brought by the left joins). Hope it helps... if it does not, ask again. Almost it, but - there's no guarantee that A field is sorted or unique... :) because of that I ask how to get row number :) On Fri, 2003-08-01 at 13:44, Robert Partyka wrote: Ron Johnson wrote: No, but slightly ambiguous, at least for my old brain. I will try to by more unequivocal this time :) Shridhar Daithankar wrote: select oid,name from a; I know it, but i have to have not oid's but row numbers :) such like : table test offset | value ---+ 1 |AC43 2 |AC4X 3 |AX43 4 |ACX3 n |XC4A the best will be without using sequence :) Shridhar Daithankar wrote: I didn't get that.. could you please elaborate? Franco Bruno Borghesi wrote: And about the rows before and after that you ask, I don't understand... based on what you mean *before* and *after*? you don't have an order by clause. And what do you mean with I know that in result is record with e.g. uid='AC13A1'? You know this uid *before* sending the query? is it part of your where statement? can you use this value as a hard coded condition for a subquery? Ok, so its goes something like that: lets say i have select query: select a,b,uid from foo where c='bar' order by a; with results like that: a | b | uid ++-- 2 |x | AC01 2 |w | AC43 4 |d | AC88 4 |a | AC13 ... 7 |c | AC22 and lets say I selected this before and I know that there is uid='AC88'; and in another connection (in lets say next requested www php script ) without selecting all this data or even full list of only uid`s and making sequence scan row by row I wont to get something like that from select I have write above: a | b | uid ++-- 2 |w | AC43 4 |d | AC88 4 |a | AC13 (3 rows) if there is row before and row next of uid='AC88' or a | b | uid ++-- 2 |w | AC43 4 |d | AC88 (2 rows) if uid='AC88' is last one row or a | b | uid ++-- 4 |d | AC88 4 |a | AC13 (2 rows) if uid='AC88' is first row I hope its more understandable than before :) regards Robert 'BoBsoN' Partyka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Batch processing
Maybe the best option (specialy if you need users to control the jobs) would be to create a table like this: CREATE TABLE batchjobs ( id SERIAL, sql TEXT NOT NULL, done BOOLEAN NOT NOOL DEFAULT false ); Then you create a pl/pgsql function that iterates every undone record from this table and EXECUTEs the query from the sql field (and sets the done field to true). You can run this process from crontab let's say, every 15 minutes (only if this process is not already running) with a echo SELECT * FROM runJobs();| psql ... Hope this helps. On Tue, 2003-07-29 at 05:28, [EMAIL PROTECTED] wrote: I'm in the need of something similar to Oracle's batch processing, in Postgres. I would like to have something like a queue of requests (SQL selects), run one by one, so not to overload the server -- every SELECT is 'intensive'. How would you implement such a thing? ciao Guido ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster signature.asc Description: This is a digitally signed message part
Re: [GENERAL] 0/1 vs true/false
and what about boolean attirbutes in where clauses? Is any difference between -SELECT ... WHERE boolean_field and -SELECT ... WHERE boolean_field=true ? On Wed, 2003-07-23 at 11:48, Tom Lane wrote: Franco Bruno Borghesi [EMAIL PROTECTED] writes: This makes me wonder, what about 't' and 'f'?... will they disappear in newer versions of postgreSQL? No. See my comment about I/O representations being outside the spec. There is no inconsistency in the following examples: regression=# select true; bool -- t (1 row) regression=# select t; ERROR: attribute t not found regression=# select 't'::boolean; bool -- t (1 row) regression=# select '1'::boolean; bool -- t (1 row) regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [GENERAL] SELECT @@IDENTITY
I always use Steve's method (it's easier), but if you still want to do it in the sql-server way, you can use OIDs to fetch the id of the last INSERTed row. I don't know which programming language you are using, but are some examples: *plpgsql DECLARE myOid INTEGER; identity INTEGER; BEGIN INSERT INTO table VALUES (value1, value2, ... valueN); GET DIAGNOSTICS myOid=RESULT_OID; SELECT INTO identity pkfield FROM table WHERE OID=myOid; //do something with identity END; *php --- $res=pg_exec($conn, INSERT INTO table VALUES (value1, value2, ... valueN)); $oid=pg_getlastoid($res); $res=pg_exec($conn, SELECT pkfield AS id FROM table WHERE OID=$oid); $row=pg_fetch_array($res, 0, PGSQL_ASSOC); $identity=$row[id]; //do something with $identity *libpq PGResult *res=PQexec(con, INSERT INTO table VALUES (value1, value2, ... valueN)); Oid oid=PQoidValue(res); PQclear($res); char buffer[255]; sprintf(buffer, SELECT pkfield FROM table WHERE OID=%u, oid); res=PQexec(con, buffer); int identity=atoi(PQgetvalue(res, 0, 0)); PQclear($res); As you see, fetching from the sequence before inserting the row seems to be the easiest way. On Mon, 2003-06-23 at 15:11, Steve Lane wrote: On 6/23/03 12:58 PM, Robert J. Sanford, Jr. [EMAIL PROTECTED] wrote: Okay, I did a quick search through both the general and SQL lists(1,2) trying to determine if there is a PostgreSQL construct equivalent to Microsoft SQL Server's SELECT @@IDENTITY. After performing an insert the database caches the last sequence number for each connection and the select retrieves that value. Thus if connection A and connection B each inserted into the same table they would each know what the value of the sequence was immediately after their insert. Is there any direct support for this? I know that I can manually code to select the nextval of a sequence but the syntactic sugar of SELECT @@IDENTITY is really nice. Many thanks! Hi Robert: You'll want to consult the postgres docs about sequences. The function you're looking for is called currval(), and selects just what you want -- the most recent value delivered for a sequence over a given connection. (I've always been curious about how well this behaves, or not, with connection pooling). -- sgl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) signature.asc Description: This is a digitally signed message part
