[GENERAL] Re: [GENERAL] conexão no windows 7
2012/2/15 vossistemas vossiste...@ibest.com.br Instalei o Postgresql 8.3 no windows 7 como servidor. Em uma estação com xp estou tentando me conectar e ocorre a seguinte mensagem: FATAL: no pg_hba.conf entry for host 192.168.1.51, user Vilson, database postgres, SSL off . No servidor com windows 7 está configurado: postgresql.conf: listen_addresses = '*' pg.hba.conf: host all all 192.168.1.0/24 o ip do servidor : 192.168.1.48 o ip da estação: 192.168.1.51 da estação chamo o ip do servidor 192.168.1.48 Já configurei em várias máquinas e não deu problema. Esta é a primeira vez que estou configurando no WINDOWS 7 ULTIMATE Vilson Zin VOS Software Ltda Seems that you forgot the METHOD portion, usually 'md5': # host all all 192.168.1.0/24 md5 # ^ Regards, diego -- View this message in context: http://postgresql.1045698.n5.nabble.com/conexao-no-windows-7-tp5487197p5487197.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Changed SSL Certificates
On Fri, Apr 8, 2011 at 1:42 PM, Carlos Mennens carlos.menn...@gmail.comwrote: I had self signed SSL certificates on my database server but since then removed them and received updated certificates from the security team. I removed (backedup) the old server.crt server.key and now have db1_ssl.crt db1_ssl.key in the identical location as the old SSL certificates. I then went to /etc/postgres/8.4/main and removed the old symbolic links for the old certificates and generated new symbolic links: ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key I then restarted PostgreSQL and got the following error: 2011-04-08 09:54:34 EDT FATAL: could not load server certificate file server.crt: No such file or directory 2011-04-08 10:00:43 EDT FATAL: could not load server certificate file server.crt: No such file or directory I looked for anywhere else in /var/lib/postgres/ /etc/postgres/ but can't find anything else that's calling the old certificates. I changed the ownership on the certificates and symbolic links to either root or postgres and nothing worked. It fails to start with the following error: root@db1:/# /etc/init.d/postgresql start Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2011-04-08 12:36:54 EDT FATAL: could not load server certificate file server.crt: No such file or directory ... failed! I checked the documentation page: http://www.postgresql.org/docs/8.4/static/libpq-ssl.html Table 30-4. Libpq/Client SSL File Usage FileContentsEffect ~/.postgresql/postgresql.crtclient certificate requested by server ~/.postgresql/postgresql.keyclient private key proves client certificate sent by owner; does not indicate certificate owner is trustworthy ~/.postgresql/root.crt trusted certificate authorities checks server certificate is signed by a trusted certificate authority ~/.postgresql/root.crl certificates revoked by certificate authorities server certificate must not be on this list Can anyone tell me what I'm doing wrong or missing here? I can't disable SSL per DoD requirements sadly. -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi, When linking to the certificate and key you should specify the full path. ln -s /etc/ssl/certs/db1_ssl.crt /full/path/to/db1_ssl.crt ln -s /etc/ssl/private/db1_ssl.key /full/path/to/db1_ssl.key HTH, diego
Re: [GENERAL] Changed SSL Certificates
On Fri, Apr 8, 2011 at 2:21 PM, Carlos Mennens carlos.menn...@gmail.comwrote: On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz dsch...@gmail.com wrote: Hi, When linking to the certificate and key you should specify the full path. ln -s /etc/ssl/certs/db1_ssl.crt /full/path/to/db1_ssl.crt ln -s /etc/ssl/private/db1_ssl.key /full/path/to/db1_ssl.key Thanks for the quick reply Diego. I posted the commands above and I used the full path to the certificates as you can see. Here's the info: lrwxrwxrwx 1 postgres postgres 26 Apr 8 10:43 db1_ssl.crt - /etc/ssl/certs/db1_ssl.crt lrwxrwxrwx 1 postgres postgres 28 Apr 8 10:50 db1_ssl.key - /etc/ssl/private/db1_ssl.key The 1st part is just the symbolic link referenced in /var/lib/postgresql/8.4/main but you can see it knows to reference the symbolic links to /etc/ssl/... I'm thinking there's some random configuration file for PostgreSQL that has pointers to the old server.crt and server.key files but I've searched /etc/postgres/ and /var/lib/postgresql/8.4/main completely and can't find it what so ever. I am not authorized to disable SSL per DoD standards / requirements sadly. Any thing else I am missing? I can't be the 1st person to switch SSL certificates during utilization. Make sure the files have the right ownership and permissions. It looks like ownership is correct (postgres:postgres) but permissions might be too loose. Try chmod 400 on your key and certificate and see what happens. cheers, diego
Re: [GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Mon, Nov 1, 2010 at 1:36 PM, Carlos Mennens carlos.menn...@gmail.com wrote: I did an upgrade on my database server this past weekend and the database fails to start. I checked /var/log/postgresql and found the reason: [r...@slave ~]# ps aux | grep postgres root 5189 0.0 0.0 8128 956 pts/0 S+ 12:28 0:00 grep postgres [r...@slave ~]# /etc/rc.d/postgresql start :: Starting PostgreSQL [BUSY] server starting [DONE] [r...@slave ~]# ps aux | grep postgres root 5205 0.0 0.0 8128 960 pts/0 R+ 12:28 0:00 grep postgres [r...@slave ~]# tail -n 50 /var/log/postgresql.log FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. Does anyone know if this is a issue with PostgreSQL or with the way Arch Linux packages the upgrade? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi all, Just upgraded PostgreSQL yesterday, from 8.4.5 to 9.0.1 on Debian. This is not to start a flame on linux distributions but instead I just want to stand out that the hard work done over the years by Martin Pitt on the excellent PostgreSQL debian packages isn't sufficiently appreciated. Regards, diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why Select Count(*) from table - took over 20 minutes?
On Tue, Oct 26, 2010 at 2:18 PM, Ozz Nixon ozzni...@gmail.com wrote:
I am the only user on this system right now, and one table select count(*)
took over 20 minutes:
wikitags exists and has 58,988,656 records.
Structure (in pascal) is:
quer.SQL.Add('create table '+DBTags+' (');
quer.SQL.Add(' pagename '+SQL_TITLE+'(100) not null,');
quer.SQL.Add(' tagword '+SQL_TITLE+'(15) not null,');
quer.SQL.Add(' soundex2 '+SQL_TITLE+'(4) not null,');
quer.SQL.Add(' metaphone '+SQL_TITLE+'(15) not null,');
quer.SQL.Add(' metaphone2 '+SQL_TITLE+'(22) not null,');
quer.SQL.Add(' carverphone '+SQL_TITLE+'(22) not null,');
quer.SQL.Add(' instances '+SQL_INT32+' not null,');
if SQL_NAME_PRIMARY_KEYS then quer.SQL.Add(' constraint '+DBTags+'_PK');
quer.SQL.Add(' primary key(pagename, tagword, instances)');
quer.SQL.Add(')');
where SQL_TITLE = 'varchar', SQL_IN32 = 'int'
I have hung off indexes for each column, to resolve my previous performance
issue from 3+ weeks ago. However, COUNT() is still dog slow - this table is a
write once, read many... *never* update, nor delete.
Any suggestions?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
You could try something like what's suggested in this blog post:
http://jakub.fedyczak.net/post/26
I didn't actually tried it, but I think it should work ok.
cheers,
diego
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extracting data from BYTEA column to binary file using libpq
On Tue, Sep 14, 2010 at 6:01 PM, Julia Jacobson julia.jacob...@arcor.de wrote:
Hello everybody out there using PostgreSQL,
What is the problem with the following C++ code for the extraction of data
from a BYTEA column to a binary file?
#include stdlib.h
#include stdio.h
#include iostream
#include fstream
#include libpq-fe.h
using namespace std;
main ()
{
PGconn *conn;
conn = PQconnectdb(hostaddr='databaseserver.com' port='5432'
dbname='test_db' user='test_user' password='secret');
int size;
const char* contents;
PGresult* res;
res = PQexecParams(conn,
SELECT filecontent FROM pictures WHERE picture_id='3',
0, NULL,NULL,NULL,NULL,
1);
if (res PQresultStatus(res)==PGRES_TUPLES_OK)
{
size = PQgetlength(res, 0, 0);
contents = PQgetvalue(res, 0, 0);
}
ofstream myFile (picture.jpg, ios::out | ios::binary);
myFile.write (contents);
myFile.close();
}
Thanks in advance,
Julia
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
In addition to what Daniel Verite said, I think you should use numeric
IP address instead of the host name, or consider replacing 'hostaddr'
with 'host' if you plan to use host names.
'hostaddr' is meant to be used when you want to avoid the name resolution step.
conn = PQconnectdb(host='databaseserver.com' port='5432'
dbname='test_db' user='test_user' password='secret');
regards,
diego
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
On Thu, Apr 8, 2010 at 6:34 PM, sunpeng blueva...@gmail.com wrote: once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_class.database = pg_database.oid; anybody knows how to do it? another question:how postgresql internal knows which relations belongs to which database? thanks hi, You can use the -E option for psql, so it will output all querys executed behind the scenes when you use meta-commands like \dt. You can then copy and modify those querys to better suit your needs. Example $ psql -E mydb psql (8.4.3) Type help for help. mydb=# \dt .. (the query that gets executed is appears here).. List of relations Schema | Name | Type | Owner +--+---+ public | sometable | table | myname HTH, diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problemas con la copia de seguridad PostgresSQL
2010/2/13 sergio barrera xpbarr...@hotmail.com Buenas compañeros! Tengo un problema al cual no le encuentro la lógica. Mi idea es realizar una copia de seguridad de una base de datos PostgresSQL diaria bajo el sistema operativo Linux-Ubuntu 9. Para ello primero, he ejecutado el siguiente comando en el terminal, obteniendo una respuesta esperada: $ pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f /home/sbarrera/BACKUP/copia_BD_Openbravo/fechaC.backup openbravo Al ejecutar dicha sentencia, se me genera un archivo con nombre fechaC.backup, que es el resultado esperado. El segundo paso es realizar un script para que pueda ser lanzado diariamente por el cron y ahí es donde tengo el problema. El script que me he generado(respaldoBD.sh) es tan sencillo como: #!/bin/sh pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f /home/sbarrera/BACKUP/copia_BD_Openbravo/fechaC.backup openbravo El problema es que cuando ejecuto en el terminal $source respaldoBD.sh me reporta el siguiente error : »: FATAL: no existe la base de datos «openbravoa base de datos «openbravo pg_dump: *** se abortó por un error No entiendo esto si estoy ejecutando la misma sentencia pero en este caso desde un script. ¿Porqué podria pasar esto? Nota : tengo el archivo de .pgpass con permisos 0600 y de valor : *:*:*:postgres:postgres *:*:*:*:postgres Espero vuestra ayuda porque ya no se lo que probar, Muchas Gracias ¿Desde el crontab de qué usuario se ejecuta el script? El de root? ¿Podrías mostrarnos la línea que tienes en el crontab? Saludos, diego
Re: [GENERAL] Problemas con la copia de seguridad PostgresSQL
2010/2/13 sergio barrera xpbarr...@hotmail.com
Buenas compañeros!
Tengo un problema al cual no le encuentro la lógica. Mi idea es realizar
una copia de seguridad de una base de datos PostgresSQL diaria bajo el
sistema operativo Linux-Ubuntu 9. Para ello primero, he ejecutado el
siguiente comando en el terminal, obteniendo una respuesta esperada:
$ pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f
/home/sbarrera/BACKUP/copia_BD_Openbravo/fechaC.backup openbravo
Al ejecutar dicha sentencia, se me genera un archivo con nombre
fechaC.backup, que es el resultado esperado. El segundo paso es realizar
un script para que pueda ser lanzado diariamente por el cron y ahí es donde
tengo el problema. El script que me he generado(respaldoBD.sh) es tan
sencillo como:
#!/bin/sh
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f
/home/sbarrera/BACKUP/copia_BD_Openbravo/fechaC.backup openbravo
El problema es que cuando ejecuto en el terminal
$source respaldoBD.sh
me reporta el siguiente error : »: FATAL: no existe la base de datos
«openbravoa base de datos «openbravo
pg_dump: *** se abortó por un error
No entiendo esto si estoy ejecutando la misma sentencia pero en este caso
desde un script. ¿Porqué podria pasar esto?
Nota : tengo el archivo de .pgpass con permisos 0600 y de valor :
*:*:*:postgres:postgres
*:*:*:*:postgres
Espero vuestra ayuda porque ya no se lo que probar, Muchas Gracias
Si usas el crontab de root, puedes invocar a pg_dump con el comando su.
Sería mas o menos asi:
(dentro del script)
# volcado en formato 'archive'
su sbarrera -c '/usr/pg_dump -Fc mibasededatos'
/algun/lugar/backus/backup-${NOW}.dmp
# tambien volcar en texto plano, sql
su sbarrera -c '/usr/bin/pg_dump mibasededatos'
/algun/lugar/backus/backup-${NOW}.sql
Saludos,
diego
Re: [GENERAL] initdb fails on Windows with encoding=LATIN1
On Sat, Jul 18, 2009 at 1:47 PM, Abraham, Dannydanny_abra...@bmc.com wrote: It runs fine with WIN1252. The error message says something about lc_type. Tried with various code pages: 850,1252. Nothing goes through. Any thoughts? Would you care to mention the PostgreSQL version you are using? Also, the exact copypasted error message would be handy. regards, diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error stranger
On Mon, Apr 20, 2009 at 9:25 AM, paulo matadr saddon...@yahoo.com.br wrote: I get this error when make a select below SELECT * FROM batch.funcionalidade_iniciada where proi_id = x ERROR: missing chunk number 0 for toast value 458755 SQL state: XX000 Looks like your database repository is corrupted and/or your disk is failing. I'd suggest you to 1) look at the logs for error messages 2) fix hardware problems (if any) 3) if you don't have UPS, buy one 4) restore your database from last backup regards, diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
On Wed, Apr 1, 2009 at 3:16 PM, Jennifer Trey jennifer.t...@gmail.com wrote: I even wrote down the password when I installed the DB and now it doesn't work! I have logged in once to the DB through pgAdmin, and choose to store the password and it said that it was stored in plain text.. where can I find it? in what file?? I even created a DB that I haven't used yet so I am certain I have been in there.. what has happend? Most importantly, where can I find the password if it was stored? / Jennifer It's in the docs http://www.postgresql.org/docs/8.3/static/libpq-pgpass.html Cheers, diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
On Wed, Apr 1, 2009 at 3:50 PM, Jennifer Trey jennifer.t...@gmail.com wrote: Sorry, for the confusion.. just want to make something I wrote more clear: I tried to add a server and it required the use of a password and not to add a password.. upon the creation and connection, it says that it failed. The Server gets created but I cannot log in to it, even though its new... and also, I found it (the password) but I cannot log in with it anyway... / Jennifer -- Forwarded message -- From: Jennifer Trey jennifer.t...@gmail.com Date: Wed, Apr 1, 2009 at 10:47 PM Subject: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work! To: pgsql-general@postgresql.org Yes, I found it.. but I cannot log in? Is there any simple way just to scratch the server and add a new one? thru pgAdmin please? I tried to Add Server but it requires a password too!? and thats not working either.. why does a new server require a new password? To Raymonds last, I am using pgAdmin, and that file I accidentally attached instead of an image I was planning to attach.. To be able to log in with pgAdmin, you have to configure the postgres server to listen in a TCP socket. To do this, you have to edit its configuration file (postgresql.conf), located probably in the postgresql installation directory (not sure as I don't use windows, might be slightly different). You just have to add a single line listen_addresses = '*' then restart the service and pgAdmin should be able to log in. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] posible BUG on psql... or maybe worst
On Sat, Dec 6, 2008 at 8:50 AM, Martin Marques
[EMAIL PROTECTED]wrote:
I was making some table creation on one of our development DB and found
that psql's \dt has problems showing all tables available. Basically, if you
have to tables with the same name in different schemas, only one will be
listed (the one on the schema that is first in the search_path).
IMHO, \dt should show all the tables per-schema.
Now what I can't find is where the problem is. \dt executes this query:
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
The query looks ok, but it doesn't bring the 2 tables in the list.
Hi,
Schemas are a lot like directories at operating system level (except that
can't be nested).
When you ls (or dir) in /home/martin/ , normally you don't expect to see
/home/johnny/ listed as well.
But if you really want to see all tables, try adjusting search_path like
this:
SET search_path to myschema1,myschema2,public;
Then it should list all relations as you expect.
Re: [GENERAL] posible BUG on psql... or maybe worst
On Sat, Dec 6, 2008 at 10:00 AM, Martin Marques [EMAIL PROTECTED]wrote: Diego Schulz escribió: Hi, Schemas are a lot like directories at operating system level (except that can't be nested). When you ls (or dir) in /home/martin/ , normally you don't expect to see /home/johnny/ listed as well. But if you really want to see all tables, try adjusting search_path like this: SET search_path to myschema1,myschema2,public; Then it should list all relations as you expect. Sorry, forgot to say that I SET search_path acordinlly to see relations from both schemas. But whan the table has the same name I only get the one from the first schema in the search_path. I can confirm the behaviour you described. \dt+ *.contactos List of relations Schema | Name| Type | Owner | Description +---+---+-+- prueba | contactos | table | dschulz | public | contactos | table | dschulz | dschulz=# \dt+ List of relations Schema | Name | Type | Owner | Description ---++---++- prueba | contactos | table | dschulz | public | bitacora | table | dschulz | public | documentos | table | dschulz | public | documentos_tipos | table | dschulz | ... (snip) ... (no table public.contactos listed here) dschulz=# select version(); version - PostgreSQL 8.3.5 on i386-portbld-freebsd7.1, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD] (1 row) But you can always use \dt+ *. to list all relations in all schemas. cheers
Re: [GENERAL] Using Postgres to store high volume streams of sensor readings
On Fri, Nov 21, 2008 at 9:50 AM, Ciprian Dorin Craciun [EMAIL PROTECTED] wrote: Currently I'm benchmarking the following storage solutions for this: * Hypertable (http://www.hypertable.org/) -- which has good insert rate (about 250k inserts / s), but slow read rate (about 150k reads / s); (the aggregates are manually computed, as Hypertable does not support other queries except scanning (in fact min, and max are easy beeing the first / last key in the ordered set, but avg must be done by sequential scan);) * BerkeleyDB -- quite Ok insert rate (about 50k inserts / s), but fabulos read rate (about 2M reads / s); (the same issue with aggregates;) * Postgres -- which behaves quite poorly (see below)... * MySQL -- next to be tested; I think it'll be also interesting to see how SQLite 3 performs in this scenario. Any plans? regards diego
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Tue, Nov 4, 2008 at 9:57 AM, Lennin Caro [EMAIL PROTECTED] wrote: Hi all, I'm re-writing some functions and migrating bussines logic from a client application to PostgreSQL. I expected something like this to work, but it doesn't: -- simple table CREATE TABLE sometable ( id SERIAL PRIMARY KEY, text1 text, text2 text ); CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ; $$ LANGUAGE SQL ; Please note the use of RETURNING clause. If I put a SELECT 1; after the INSERT, the function works (but doesn't returns any useful value :) I need the function to return the last insert id. And yes, I'm aware that the same can be achieved by selecting the greatest id in the SERIAL secuence, but is not as readable as RETURNING syntax. And no, for me it's not important that RETURNING is not standard SQL. Does anyone knows why RETURNING doesn't works inside SQL functions? Any advise will be very appreciated. TIA. diego Hi.. what version of postgres you have? I'm using 8.3.3. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Tue, Nov 4, 2008 at 2:38 AM, Tom Lane [EMAIL PROTECTED] wrote: Diego Schulz [EMAIL PROTECTED] writes: On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell [EMAIL PROTECTED] wrote: Just curious - what have you got against currval()? It seems to me that it would make your life easier I simply don't like having to cast from BIGINT to INTEGER, Under what circumstances do you need an explicit cast? regards, tom lane When I want the function to return the same type as the index of the table (normally SERIAL), and I have other functions that rely on the datatype returned. To avoid casting I can simply change the function's signature to return BIGINT (to match currval() return type) and the problem vanishes but.. then I have more functions that needs to be adapted. Maybe I'm a bit paranoid of BIGINT's performance penalty too, as the set of functions will be heavily used, but honestly, this fear completely lacks foundation. Just to make it clear, the main reason for this thread was curiosity :) Thank you for your time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error al crear una base en español.....
On Tue, Nov 4, 2008 at 6:32 PM, Eduardo Arévalo [EMAIL PROTECTED] wrote: hola quiero crear una base que soporte caracteres en español y le doy este comando pero no crea la base sino me manda este error: -bash-3.2$ ./createdb --encoding=LATIN1 sig_spa_prueba Password: createdb: database creation failed: ERROR: encoding LATIN1 does not match server's locale en_US.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. gracias Perdón por responder con una pregunta, pero cual es el problema con UTF-8 ? Deberías poder lograr lo mismo con UTF-8. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INSERT with RETURNING clause inside SQL function
Hi all, I'm re-writing some functions and migrating bussines logic from a client application to PostgreSQL. I expected something like this to work, but it doesn't: -- simple table CREATE TABLE sometable ( id SERIAL PRIMARY KEY, text1 text, text2 text ); CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ; $$ LANGUAGE SQL ; Please note the use of RETURNING clause. If I put a SELECT 1; after the INSERT, the function works (but doesn't returns any useful value :) I need the function to return the last insert id. And yes, I'm aware that the same can be achieved by selecting the greatest id in the SERIAL secuence, but is not as readable as RETURNING syntax. And no, for me it's not important that RETURNING is not standard SQL. Does anyone knows why RETURNING doesn't works inside SQL functions? Any advise will be very appreciated. TIA. diego -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
Forgot to mention: using 8.3.3 on FreeBSD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Mon, Nov 3, 2008 at 10:24 PM, Raymond O'Donnell [EMAIL PROTECTED] wrote:
On 04/11/2008 01:20, Diego Schulz wrote:
I also tried this (somewhat silly) syntax to circumvent the issue
without resorting in currval:
Just curious - what have you got against currval()? It seems to me that
it would make your life easier
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--
I simply don't like having to cast from BIGINT to INTEGER,
as currval returns BIGINT while the index of my table is INTEGER.
I think isn't as readable and elegant as the single INSERT ... RETURNING value.
Being the only choice at this time (that I'm aware of) I'm using
something like this:
SELECT CAST(CURRVAL('mytable_id_seq') AS INTEGER);
If I can avoid messing with sequence manipulation functions, surely I will.
Cheers!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
On Mon, Nov 3, 2008 at 8:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Diego Schulz [EMAIL PROTECTED] writes: I expected something like this to work, but it doesn't: CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ; $$ LANGUAGE SQL ; This case was implemented last week. In existing release branches you'll need to use currval or some other workaround to collect the serial value. regards, tom lane Thank you Tom. Happy to read it's implemented now! :) After re-reading the docs: ...the final command _must be a SELECT_ that returns whatever is specified as the function's return type I also tried this (somewhat silly) syntax to circumvent the issue without resorting in currval: CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ SELECT id FROM ( INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ) ; $$ LANGUAGE SQL ; and CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS INTEGER AS $$ SELECT last_insert_id FROM ( INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id ) AS last_insert_id ; $$ LANGUAGE SQL ; As expected, none of them works as *I* expected. You know, fools keep trying.. and eventually hit :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
