[GENERAL] Select rotate in PostgreSql
Hi all, I don't know how to ask for this. I need to rotate a Select in PostgreSql, just like this: http://dpaste.com/1021691/ I can achieve this easily without a procedure? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get faster queries in the database?
Hi, I've developed a system that was not to work online, but now it is online and it is degrading due to bad design choices. Here is the thing. I've the database build in vertical mode. I will justo explain what I mean with that. Attribute | Value site_name | Some site name1 uri | Some uri1 job_title | Some job title1 job_description | Some job description1 country_ad | Some country1 location_ad | Some location 1 The above is the vertical mode that I was talking about. It is actually more complicated because it deals with JOINS. The SELECT below is the SELECT that I use to rotate the data. [query] SELECT md5(site_name.uri) as hash, NULLIF(site_name.site_name, ''::text) AS site_name, site_name.uri::text AS uri, NULLIF(job_title.job_title, ''::text) AS job_title, NULLIF(job_description.job_description, ''::text) AS job_description, NULLIF(country_ad.country_ad, ''::text) AS country_ad, NULLIF(zone_ad.zone_ad, ''::text) AS zone_ad, NULLIF(location_ad.location_ad, ''::text) AS location_ad, date_inserted.date_inserted FROM ((tdir_uris date_inserted JOIN ( SELECT tdir_uris_text.id_category, tdir_uris_text.uri, tdir_uris_text.n_text AS site_name -- Ter em atencao, este select tem aqui metido o id_category p usar no WHERE final deste select FROM tdir_uris_text WHERE ((tdir_uris_text.id_data)::text = 'site_name'::text) ) site_name ON (((site_name.uri)::text = (date_inserted.uri)::text))) LEFT JOIN ( SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS job_title FROM tdir_uris_text WHERE ((tdir_uris_text.id_data)::text = 'job_title'::text) ) job_title ON (((job_title.uri)::text = (site_name.uri)::text))) LEFT JOIN ( SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS job_description FROM tdir_uris_text WHERE ((tdir_uris_text.id_data)::text = 'job_description'::text) ) job_description ON (((job_description.uri)::text = (site_name.uri)::text))) LEFT JOIN ( SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS country_ad FROM tdir_uris_text WHERE ((tdir_uris_text.id_data)::text = 'country_ad'::text) ) country_ad ON (((country_ad.uri)::text = (site_name.uri)::text))) LEFT JOIN ( SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS zone_ad FROM tdir_uris_text WHERE ((tdir_uris_text.id_data)::text = 'zone_ad'::text) ) zone_ad ON (((zone_ad.uri)::text = (site_name.uri)::text))) LEFT JOIN ( SELECT tdir_uris_text.uri, tdir_uris_text.n_text AS location_ad FROM tdir_uris_text WHERE ((tdir_uris_text.id_data)::text = 'location_ad'::text) ) location_ad ON (((location_ad.uri)::text = (site_name.uri)::text))) WHERE site_name.id_category = 5 [/query] With the SELECT I build a VIEW to show me something like this: site_name | uri | job_title | job_description | country_ad | location_ad Some site name1 | Some uri1 | Some job title1 | Some job description1 | Some country1 | Some location 1 Some site name2 | Some uri2 | Some job title2 | Some job description2 | Some country2 | Some location 2 Some site name3 | Some uri3 | Some job title3 | Some job description3 | Some country3 | Some location 3 Some site name4 | Some uri4 | Some job title4 | Some job description4 | Some country4 | Some location 4 Some site name5 | Some uri5 | Some job title5 | Some job description5 | Some country5 | Some location 5 My question is, how can I get the queries faster. It is possible to do it with INDEXES or it is better to search for other approach? I simple SELECT using a LIMIT do paginate is taking 5 minutes. Any ideas on where to start? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to isolate the result of SELECT's?
Hi, I need to do an operation that I will use some SELECT's and get the results, but I want to have sure that those tables have not been changed with INSERT's or UPDATES during the operation. Example: BEGIN OPERATION Select field from table1; ... Select other_field from table2; ... END OPERATION How can I lock these tables to assure that the tables are not getting INSERTS's or UPDATE's during the operation? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Complex transactions without using plPgSQL Functions. It is possible?
Hi, I'm writing a web application that uses PostgreSQL and I need to do some operations where I read/write to 3 tables in the same transaction. To do this I need to store the values of variables and I'm not sure if it is possible to do this without using plPgSQL. [code] SELECT count(email) INTO vCONTA_HIST FROM am_newsletter_hist_alter WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA; IF vCONTA_HIST = 0 THEN vNUM_ALTER := 1; ELSE SELECT MAX(num_alter) INTO vNUM_ALTER_ACT FROM am_newsletter_hist_alter WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA LIMIT 1; vNUM_ALTER := vNUM_ALTER_ACT + 1; END IF; [/code] This is the plPgSQL code that I need to write in Python. It is possible to do this without using PlPgSQL? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get a signal from the database when a INSERT INTO is done?
Hi all, I'm designing an application in Python and using PostgreSQL. This is a mixed question Python/PostgreSQL... I need to get a signal in my python application when a new insert is done. How can this be done, any clues? Best Regards, André -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to store variable data in a field?
Hi all, I need to create a table to store Twitter accounts information, Facebook and other social networks details for API login. I need to know if it is possible to store the account details(account_details field) in a field that contains something like an array. I need to store this data in an array like field because the details for each social network accounts are different. What is my best choice for the field account_details? CREATE TABLE account ( id_account int4 NOT NULL, id_account_type int4 NOT NULL, n_account varchar(50) NOT NULL, account_details varchar NOT NULL, comment varchar(2000), PRIMARY KEY(id_account,id_account_type) ); I will need to store something like this: Twitter: array(account_name = xpto, hash1 = 3432454355, megahash = dfcf786fds987fds897) Facebook: array(account_name = xpto, fb_special_hash = dsdsad4535, fb_security_hash = dsadsad454355, fb_extended_hash = sdasfe5r4536556fsgg) It is possible to put something like that in a field? If yes, what datatype should I choose? Pros and cons of doing this? Best Regards, -- 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] Backup database remotely
Hi all, To do this backup remotely we need to open the 5434 port in the Firewall? Best Regards, On Mon, Feb 6, 2012 at 5:28 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Fanbin Meng fanbin.m...@kiltechcontrols.com wrote: I installed the PostgreSql9.0 in windows 7 with one click installer. How can i backup another PostgreSql server database remotely via an internet connection . I trid add a connection to a server, but it did not work. Don't know waht you mean with 'add a connection to a server'. You should use something like: pg_dump -h other_host (or pg_dumpall) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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
[GENERAL] How to escape to quotes on Insert into?
Hi, I need to escape quotes on an insert into that have a quote like this: http://host.com/cond'nast How can I escape ' on an insert into? Best Regards, André. -- 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] How to escape to quotes on Insert into?
Thanks for your help. It is working.
Best Regards,
On Wed, Dec 21, 2011 at 9:04 PM, Bill Moran wmo...@potentialtech.com wrote:
In response to Andre Lopes lopes80an...@gmail.com:
Hi,
I need to escape quotes on an insert into that have a quote like this:
http://host.com/cond'nast
How can I escape ' on an insert into?
It depends:
The best way is to pass the string as a parametrized query, then you don't
have to escape anything.
The second best way is to use the string escape function for whatever
language your programming in.
If you don't have either of those available, you should reconsider your
choice of language/client library, as writing your own escape functions is
bad news.
If you're forced to write the raw SQL statements for some reason, you
escape ' with a second ', so:
INSERT INTO tablename (colname) VALUES ('http://host.com/cond''nast');
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
--
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
[GENERAL] How to configure the connection timeout in PostgreSQL 8.3
Hi, I'm using PostgreSQL 8.3 and I need to reduce the timeout. How can I configure the connection timeout? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Queries take long long(10 sec.) time running
Hi, I have a vertical database schema running with an webapp. I query this database in the webapp rotating the vertical schema with a view(something like a pivot view) I know that this type of operation is very expensive, but I have the webapp running with acceptable response time for 45 days(machine uptime). Now the queries started to take much much time to execute. I use a VPS with 512MB memory, and I have Apache2, PostgreSQL 8.4, MySQL 5. The webapp don't have high traffic, have about 700 visits per day... I've run top and I see that the server is using a lot of swap. I have ordered the top to give me the used swap, the result is this: [code] top - 09:24:13 up 49 days, 22:44, 3 users, load average: 0.15, 0.12, 0.13 Tasks: 123 total, 1 running, 122 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 96.6%id, 3.3%wa, 0.0%hi, 0.0%si, 0.0%st Mem:500452k total, 490576k used, 9876k free, 496k buffers Swap: 524284k total, 343664k used, 180620k free,32428k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ SWAP COMMAND 1881 mysql 20 0 929m 20240 S 0.0 0.4 142:11.73 927m mysqld 11093 root 20 0 275m 560 S 0.0 0.0 0:03.46 275m httpd 19462 apache20 0 280m 6704 1796 S 0.0 1.3 0:02.00 273m httpd 11103 apache20 0 280m 7076 1740 S 0.0 1.4 0:07.80 273m httpd 11095 apache20 0 280m 7544 1788 S 0.0 1.5 0:08.70 273m httpd 11260 apache20 0 280m 7548 1548 S 0.0 1.5 0:07.94 273m httpd 11096 apache20 0 280m 7728 1588 S 0.0 1.5 0:08.45 272m httpd 12668 apache20 0 280m 7660 1804 S 0.0 1.5 0:07.30 272m httpd 11293 apache20 0 280m 7908 1800 S 0.0 1.6 0:08.59 272m httpd 12669 apache20 0 280m 8052 1860 S 0.0 1.6 0:07.69 272m httpd 11099 apache20 0 277m 4940 1720 S 0.0 1.0 0:08.64 272m httpd 11297 apache20 0 280m 8100 1884 S 0.0 1.6 0:09.00 272m httpd 11102 apache20 0 280m 8148 1920 S 0.0 1.6 0:09.34 272m httpd 12672 apache20 0 280m 8172 1804 S 0.0 1.6 0:07.62 272m httpd 12113 apache20 0 280m 8220 1804 S 0.0 1.6 0:07.87 272m httpd 11100 apache20 0 280m 8348 1940 S 0.0 1.7 0:08.70 272m httpd 12663 apache20 0 278m 7188 1940 S 0.0 1.4 0:07.66 271m httpd 19350 apache20 0 277m 6148 1936 S 0.0 1.2 0:07.23 271m httpd 11105 apache20 0 280m 8928 2412 S 0.0 1.8 0:08.08 271m httpd 1960 apache20 0 279m 7912 2108 S 0.0 1.6 0:05.63 271m httpd 11287 apache20 0 276m 5176 1936 S 0.0 1.0 0:08.66 271m httpd 14813 apache20 0 280m 9336 2424 S 0.0 1.9 0:03.00 271m httpd 1729 root 20 0 242m 396 252 S 0.0 0.1 1:14.37 242m rsyslogd 11304 postgres 20 0 218m 15m 15m S 0.0 3.2 1:34.18 203m postmaster 11323 postgres 20 0 218m 20m 16m S 0.0 4.2 1:58.70 198m postmaster 20149 postgres 20 0 218m 22m 17m S 0.0 4.7 0:07.35 195m postmaster 11360 postgres 20 0 218m 23m 17m S 0.0 4.8 1:14.27 194m postmaster 11604 postgres 20 0 218m 23m 17m S 0.0 4.9 1:43.92 194m postmaster 11531 postgres 20 0 218m 24m 17m S 0.0 4.9 2:29.91 194m postmaster 11628 postgres 20 0 218m 24m 17m S 0.0 5.0 2:46.56 194m postmaster 11437 postgres 20 0 218m 24m 17m S 0.0 4.9 1:38.11 194m postmaster 28295 postgres 20 0 188m 3364 2920 S 0.0 0.7 0:00.09 184m postmaster 13465 postgres 20 0 184m 140 96 S 0.0 0.0 15:07.25 183m postmaster 13466 postgres 20 0 184m 596 300 S 0.0 0.1 6:14.63 183m postmaster 13460 postgres 20 0 184m 340 220 S 0.0 0.1 5:14.39 183m postmaster 13555 postgres 20 0 218m 35m 17m S 0.0 7.3 1:28.89 183m postmaster 13464 postgres 20 0 184m 3332 3168 S 0.3 0.7 16:38.80 180m postmaster 11761 postgres 20 0 218m 37m 17m S 0.0 7.7 1:41.28 180m postmaster 11560 postgres 20 0 218m 38m 17m S 0.0 7.8 1:37.13 180m postmaster 12914 postgres 20 0 218m 39m 17m S 0.0 8.1 1:49.34 179m postmaster 11305 postgres 20 0 202m 24m 17m S 0.0 5.0 1:31.30 178m postmaster 29837 postgres 20 0 188m 10m 8332 S 0.0 2.1 0:01.42 178m postmaster 12666 postgres 20 0 218m 40m 17m S 0.0 8.4 0:59.64 177m postmaster 19639 postgres 20 0 216m 41m 17m S 0.0 8.5 1:38.68 175m postmaster 11373 postgres 20 0 218m 44m 18m S 0.0 9.2 1:39.24 173m postmaster 12196 postgres 20 0 195m 23m 16m S 0.0 4.8 1:15.28 172m postmaster 2313 postgres 20 0 202m 31m 18m S 0.0 6.4 1:09.09 171m postmaster 14947 postgres 20 0 218m 47m 18m S 0.0 9.7 0:30.29 170m postmaster [/code] This top result is ordered by swap. This is enough information for some clues on how to increase the response time in queries? Best Regards, -- 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] Queries take long long(10 sec.) time running
Thanks for the replies. I've done a vmstat 1, here is the result: r b swpd free buff cache si sobibo in cs us sy id wa st 1 1 342728 21384264 34940 960 1428 0 616 275 1 0 92 7 0 0 0 342728 15032312 4000800 5136 0 821 561 0 0 89 10 0 0 0 342728 15048312 4007200 0 0 86 51 0 0 100 0 0 0 0 342728 15792336 4027200 256 0 561 201 0 0 94 5 0 0 0 342728 15808344 4030000 036 92 60 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 366 171 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 67 44 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 351 163 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 85 52 0 0 100 0 0 0 1 342728 14808344 4087600 52840 459 200 0 0 96 4 0 0 1 342728 14596356 41408 320 63688 217 80 0 0 95 5 0 1 1 340184 10876356 41428 41440 4144 0 1155 715 0 0 82 17 0 0 3 333176 5460316 38484 10312 28 1033632 2158 1474 3 1 75 21 0 0 2 329480 6192148 31452 7688 1764 8000 1812 2140 1311 2 1 74 23 0 1 1 329112 6452136 30836 4284 2268 5828 2296 1638 799 6 0 75 19 0 0 3 328832 5972120 30356 3572 2312 5396 2316 1388 744 4 0 72 24 0 1 5 331156 5204116 30656 764 2988 2136 3036 552 391 0 0 68 32 0 1 0 329708 6524104 30100 2176 188 3256 192 1586 517 8 0 77 14 0 0 1 330748 6580104 30840 1244 1764 5112 1764 1270 509 5 0 70 25 0 1 1 330584 5888124 31204 1160 1180 2936 1184 1110 381 7 0 76 17 0 1 1 332684 5660200 34564 936 2928 14252 3452 2812 1191 9 1 61 28 0 1 2 332500 5916220 34216 860 580 2084 1160 1151 542 4 0 66 30 0 0 2 332236 5596228 33712 1636 1088 2444 1132 1665 475 10 1 64 25 0 1 1 332552 6232152 32580 1180 1192 4948 1192 1386 504 6 0 61 32 0 1 0 332748 6496192 34380 32 204 2128 204 1804 363 13 0 78 9 0 0 4 334712 6056244 34644 356 2072 4836 2168 1752 542 8 1 71 21 0 0 2 335060 5792260 35132 100 372 1284 372 506 212 0 0 76 24 0 1 0 335492 6572280 36352 96 480 2516 576 769 325 1 0 76 23 0 0 0 335492 6568280 3643200 0 0 264 120 0 0 100 0 0 Seems my problem is SWAP/IO... I can only solve this putting more RAM on the machine or it is possible to put down this values only adjusting some settings? Best Regards, On Wed, Dec 7, 2011 at 9:52 AM, John R Pierce pie...@hogranch.com wrote: On 12/07/11 1:27 AM, Andre Lopes wrote: This is enough information for some clues on how to increase the response time in queries? no. to optimize queries, you generally need to know what the queries are, what the relations they are using look like, and get the output of `explain analyze your query;` some random comments in passing... * whats mysql got to do with this? * a server running efficiently should be using zero swap. the fact that you have 340MB of swap used implies you need at least twice as much physical memory as you have. * VPS? as in virtual server? so your disk IO is virtualized too? this is usually bad news for getting decent database performance. -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- 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
[GENERAL] Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.
Hi,
I've installed PostgreSQL 9.0 in CentOS6 I don't have configured
anything in Postgre, I just created a user with this method:
[article]
Here is how I do to create a Postgres user with the same username as
my regular login in Linux Ubuntu.
Go to your terminal with your regular user and do:
{{{
yourusername$ sudo su - postgres
}}}
Ok, now you are as postgres user. To access to postgres do:
{{{
postgres$ psql
}}}
Now that you are in postgres terminal, do this:
{{{
postgres=# create user yourusername with createdb createrole password
'newpassword';
}}}
an then do:
{{{
postgres=# create database yourusername with owner yourusername;
}}}
And now you can do this:
{{{
postgres=# \q
postgres$ exit
yourusername$ psql
...
yourusername=# ..
}}}
Remember that this will create a postgres user with the same username
as your regular login, a password, and the privileges to create more
databases and users.
[/article]
With the method above I have no problems in enter psql but when I
try to connect with the user created with these method to a webapp I
got an error:
[code]
Exception Value:
FATAL: Ident authentication failed for user mypoatgreuser
[/code]
There is more permissions that I must to give to the user
mypoatgreuser? What could be wrong here?
Best Regards,
--
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] Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.
Hi Adrian, Thanks for the reply. My pg_hba.conf have this: [code] # TYPE DATABASEUSERCIDR-ADDRESSMETHOD # local is for Unix domain socket connections only local all all ident # IPv4 local connections: hostall all 127.0.0.1/32ident # IPv6 local connections: hostall all ::1/128 ident [/code] Some clue with this config file? Best Regards, On Fri, Dec 2, 2011 at 11:34 PM, Adrian Klaver adrian.kla...@gmail.com wrote: On Friday, December 02, 2011 3:13:41 pm Andre Lopes wrote: Hi, I've installed PostgreSQL 9.0 in CentOS6 I don't have configured anything in Postgre, I just created a user with this method: With the method above I have no problems in enter psql but when I try to connect with the user created with these method to a webapp I got an error: [code] Exception Value: FATAL: Ident authentication failed for user mypoatgreuser [/code] There is more permissions that I must to give to the user mypoatgreuser? What could be wrong here? Some pointers. The client authentication is handled here: http://www.postgresql.org/docs/9.0/interactive/auth-pg-hba-conf.html In your pg_hba.conf there is at least one authentication method set to ident. That is described here: http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#AUTH-IDENT From the sequence of commands you have given you are looking to use password authentication. For security you want md5. There may already be a line with that method in your pg_hba.conf. In pg_hba.conf first matching line wins, so if there is a line with ident first it will take precedence. If this is too confusing post your pg_hba.conf(unless of course there are security issues) and we can go from there. Best Regards, -- Adrian Klaver adrian.kla...@gmail.com -- 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] Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.
Hi John, Thanks for the replies. The problem was the ident in the host. Problem solved. Thanks a lot! Best Regards, On Fri, Dec 2, 2011 at 11:46 PM, John R Pierce pie...@hogranch.com wrote: On 12/02/11 3:41 PM, Andre Lopes wrote: My pg_hba.conf have this: [code] # TYPE DATABASE USER CIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident [/code] Some clue with this config file? see my other post. and don't use ident for 'host' connections, its not really suitable. -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- 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
[GENERAL] How to correct: ERROR: permission denied: RI_ConstraintTrigger_24966 is a system trigger
Hi, I have created a database that have a function that disable triggers on tables, but when I execute the function: (I have created the database with the same user that I'm trying to execute the function) [code] select triggerall(false); [/code] return [code] ERROR: permission denied: RI_ConstraintTrigger_24966 is a system trigger CONTEXT: SQL statement ALTER TABLE tdir_languages DISABLE TRIGGER ALL PL/pgSQL function triggerall line 14 at EXECUTE statement [/code] This is known problem? How to solve this? Best Regards, -- 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] How to correct: ERROR: permission denied: RI_ConstraintTrigger_24966 is a system trigger
Hi, I have created a database and all tables with a user, but I can't execute this alter table: [code] xxx_database= ALTER TABLE tdir_categories DISABLE TRIGGER ALL; ERROR: permission denied: RI_ConstraintTrigger_25366 is a system trigger [/code] What can I do to solve this? Best Regards, On Tue, Oct 18, 2011 at 9:08 AM, Craig Ringer ring...@ringerc.id.au wrote: On 10/18/2011 03:52 PM, Andre Lopes wrote: Hi, I have created a database that have a function that disable triggers on tables, but when I execute the function: (I have created the database with the same user that I'm trying to execute the function) [code] select triggerall(false); [/code] return [code] ERROR: permission denied: RI_ConstraintTrigger_24966 is a system trigger CONTEXT: SQL statement ALTER TABLE tdir_languages DISABLE TRIGGER ALL PL/pgSQL function triggerall line 14 at EXECUTE statement [/code] You're trying to disable triggers associated with foreign key constraints or CHECK constraints, too. You should probably skip those system triggers in your function. If you want to disable them too, I think you have to be the table owner, or maybe superuser; I don't remember and haven tested. In any case, the user you're running as doesn't have permission to disable those. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to install PGCRYPTO in PostgreSQL9
Hi, I am using CentOS and PostgreSQL9. I have an application that uses Pgcrypto. I have googled but I am not sure how can I install this in PostgreSQL9. Someone can give me a clue on this? Best Regards, -- 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] How to install PGCRYPTO in PostgreSQL9
I have installed and tried to import the SQL, but I got this: [code] [andre@andre public]$ psql -d 420 -f /usr/pgsql-9.0/share/contrib/pgcrypto.sql SET psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:9: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:14: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:19: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:24: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:29: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:34: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:39: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:44: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:49: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:54: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:59: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:64: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:72: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:77: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:85: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:90: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:98: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:103: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:111: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:116: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:124: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:129: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:137: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:142: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:150: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:155: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:163: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:168: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:176: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:181: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:189: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:197: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:202: ERROR: permission denied for language c [andre@andre public]$ [/code] What's wrong here? Best Regards, 2011/8/17 Devrim GÜNDÜZ dev...@gunduz.org: On Wed, 2011-08-17 at 06:28 -0700, Andre Lopes wrote: I am using CentOS and PostgreSQL9. I have an application that uses Pgcrypto. I have googled but I am not sure how can I install this in PostgreSQL9. If you are using RPMS, then install -contrib RPM, and then install pgcrypto using pgcrypto.sql that comes with the package. If it is the source installation, run make install under contrib/pgcrypto directory. Then again, load pgcrypto.sql to your database. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- 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] How to install PGCRYPTO in PostgreSQL9
Thanks for the reply. I have installed with the user postgres and it worked. Thanks! 2011/8/17 Adrian Klaver adrian.kla...@gmail.com: On Wednesday, August 17, 2011 6:44:31 am Andre Lopes wrote: I have installed and tried to import the SQL, but I got this: [code] [andre@andre public]$ psql -d 420 -f /usr/pgsql-9.0/share/contrib/pgcrypto.sql SET psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:197: ERROR: permission denied for language c psql:/usr/pgsql-9.0/share/contrib/pgcrypto.sql:202: ERROR: permission denied for language c [andre@andre public]$ [/code] What's wrong here? Would seem to indicate you did not install as database superuser. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Some PostgreSQL enthusiast working in holland?
Hi, I'm willing to contact a PostgreSQL developer working in Holland. I know that this is not the main reason of this list. Please contact-me by e-mail. Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using bytea field...
Hi, I'm using a bytea field to store small images in base64. I need to know if I can compare bytea rows for equality. To know for example if the file already exists in the database, this is possible with bytea? Best Regads, -- 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] PHP array to PlPgSQL arrat. How to?
Hi,
Just another question about this subject.
It is possible to compare if these 3 PlpgSQL arrays have the same
number of elements? How to get the number of elements of an PLpgSQL
array?
NOTICE: file types: {image,image,image,image,image}
NOTICE: file details: {type,width,height,html_width_height,mime}
NOTICE: values: {jpg,343,515,width=\343\ height=\515\,image/jpeg}
Best Regards,
On Sat, Mar 5, 2011 at 10:44 PM, John R Pierce pie...@hogranch.com wrote:
On 03/05/11 11:22 AM, Andre Lopes wrote:
Hi,
I need to transform an PHP array to an PlPgSQL array. The PHP array is
like this:
in a relational database, it would be far better to store that sort of thing
as a table, so you can use relational operations on it.
your example structure would fit nicely into a table like...
CREATE TABLE images (
id integer primary key,
base64 text,
image_type text,
width integer,
height integer,
mime text );
and use a view to construct the html_width_height value as it contains
redundant data
CREATE VIEW images_html (id, html_width_height)
as select id, 'width='||cast(width as text)||' height='||cast(height
as text)||'' from images;
btw, if that base64 field is in fact the binary image, I would instead
change that to `image bytea` and store the image in binary.
--
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] PHP array to PlPgSQL arrat. How to?
Hi Pavel,
Thanks for the reply. It was exactly that that I'm looking for.
Best Regards,
On Sun, Mar 6, 2011 at 3:05 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
look on array_lower and array_upper functions
http://www.postgresql.org/docs/8.2/static/functions-array.html
for one dimensional arrays - select array_upper(var,1) - array_lower(var,1)
Regards
Pavel Stehule
2011/3/6 Andre Lopes lopes80an...@gmail.com:
Hi,
Just another question about this subject.
It is possible to compare if these 3 PlpgSQL arrays have the same
number of elements? How to get the number of elements of an PLpgSQL
array?
NOTICE: file types: {image,image,image,image,image}
NOTICE: file details: {type,width,height,html_width_height,mime}
NOTICE: values: {jpg,343,515,width=\343\ height=\515\,image/jpeg}
Best Regards,
On Sat, Mar 5, 2011 at 10:44 PM, John R Pierce pie...@hogranch.com wrote:
On 03/05/11 11:22 AM, Andre Lopes wrote:
Hi,
I need to transform an PHP array to an PlPgSQL array. The PHP array is
like this:
in a relational database, it would be far better to store that sort of thing
as a table, so you can use relational operations on it.
your example structure would fit nicely into a table like...
CREATE TABLE images (
id integer primary key,
base64 text,
image_type text,
width integer,
height integer,
mime text );
and use a view to construct the html_width_height value as it contains
redundant data
CREATE VIEW images_html (id, html_width_height)
as select id, 'width='||cast(width as text)||' height='||cast(height
as text)||'' from images;
btw, if that base64 field is in fact the binary image, I would instead
change that to `image bytea` and store the image in binary.
--
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PHP array to PlPgSQL arrat. How to?
Hi, I need to transform an PHP array to an PlPgSQL array. The PHP array is like this: [quote] $arr = array( 0 = array( base64 = ddfff, image_type = jpg, width = 343, height = 515, html_width_height = 'width=343 height=515', mime = image/jpeg ), 1 = array( base64 = ddfffd, image_type = jpg, width = 343, height = 515, html_width_height = 'width=343 height=515', mime = image/jpeg ) ); [/quote] How can I pass this kinf of PHP array to PlPgSQL? Give me a clue. Best Regards, -- 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] PHP array to PlPgSQL arrat. How to?
Hi Pavel,
Thanks for the reply.
In PlpgSQL there is possible to define arrays with Key = Value, Key = Value?
Best Regards,
On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
there isn't a simple way :(
the most simply way is using string_to_array function
SELECT func(string_to_array('1,2,3,4,5',','));
Regards
Pavel Stehule
2011/3/5 Andre Lopes lopes80an...@gmail.com:
Hi,
I need to transform an PHP array to an PlPgSQL array. The PHP array is
like this:
[quote]
$arr = array(
0 = array(
base64 =
ddfff,
image_type = jpg,
width = 343,
height = 515,
html_width_height
= 'width=343 height=515',
mime = image/jpeg
),
1 = array(
base64 =
ddfffd,
image_type = jpg,
width = 343,
height = 515,
html_width_height
= 'width=343 height=515',
mime = image/jpeg
)
);
[/quote]
How can I pass this kinf of PHP array to PlPgSQL?
Give me a clue.
Best Regards,
--
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] PHP array to PlPgSQL arrat. How to?
Thanks again,
I saw this tutorial on how to get Key = Value,
http://justatheory.com/computers/databases/postgresql/key-value-pairs.html
It is not possible to use hstore to me.
Best Regards,
On Sat, Mar 5, 2011 at 7:42 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
2011/3/5 Andre Lopes lopes80an...@gmail.com:
Hi Pavel,
Thanks for the reply.
In PlpgSQL there is possible to define arrays with Key = Value, Key =
Value?
No, no directly
there are no hash array
there is a workaround a hstore module
http://www.postgresql.org/docs/9.0/static/hstore.html
attention - it doesn't allow a nested values
Regards
Pavel Stehule
for more complex values is other was - using a temp tables - you can
fill a temp table and in next step a plpgsql code use this temp table.
But it should have a performance impacts.
Best Regards,
On Sat, Mar 5, 2011 at 7:28 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hello
there isn't a simple way :(
the most simply way is using string_to_array function
SELECT func(string_to_array('1,2,3,4,5',','));
Regards
Pavel Stehule
2011/3/5 Andre Lopes lopes80an...@gmail.com:
Hi,
I need to transform an PHP array to an PlPgSQL array. The PHP array is
like this:
[quote]
$arr = array(
0 = array(
base64 =
ddfff,
image_type =
jpg,
width = 343,
height = 515,
html_width_height
= 'width=343 height=515',
mime =
image/jpeg
),
1 = array(
base64 =
ddfffd,
image_type =
jpg,
width = 343,
height = 515,
html_width_height
= 'width=343 height=515',
mime =
image/jpeg
)
);
[/quote]
How can I pass this kinf of PHP array to PlPgSQL?
Give me a clue.
Best Regards,
--
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] Transactions and ID's generated by triggers
Thanks for the reply. I will try the code tonight. Best Regards, On Sun, Feb 27, 2011 at 11:37 PM, David Johnston pol...@yahoo.com wrote: Using pl/pgsql you can: DECLARE idordinal type; BEGIN INSERT INTO tdir_uris_files RETURNING id_ordinal INTO idordinal; INSERT INTO tdir_uris_files_details (id_ordinal) VALUES (idordinal); END; Similar results are possible in other environments. If you do not have access to RETURNING for some reason you can issue a select - assuming you can identify the record in tdir_uris_files that you need. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andre Lopes Sent: Sunday, February 27, 2011 3:34 PM To: postgresql Forums Subject: [GENERAL] Transactions and ID's generated by triggers Hi, I have a situation that I dont know how to deal. I have 2 tables tdir_uris_files and tdir_uri_files_details. Please see the Image in attach. The table tdir_uris_files have the field id_ordinal that is originated by a trigger(before insert) The table tdir_uri_files_details use the field id_ordinal generated by trigger on the insert on the table tdir_uris_files Now my doubt. It is possible to do a transaction to this two tables at the same time? How can I know the value of the field id_ordinal that was generated by the trigger? Can I be able to do this? [code] BEGIN; INSERT INTO tdir_uris_files (uri, id_language, id_category, id_file_context, id_encode_format, n_file, file) values (...) INSERT INTO tdir_uri_files_details (uri, id_language, id_category, id_file_context, id_ordinal, id_file_type, id_file_detail, value) values (...); COMMIT; [/code] PS: Sorry my bad english. Best Regards, -- 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] Transactions and ID's generated by triggers
Hi, Thanks for the reply. How can I user the RETURNING without pl/sql? My PostgreSQL version is 8.3 Can you give me some example? Best Regards, On Mon, Feb 28, 2011 at 7:00 PM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 28 Feb 2011, at 24:37, David Johnston wrote: Using pl/pgsql you can: You don't need pl/pgsql for that. You can return the resultset of the first insert into a client-side variable (while still making use of the RETURNING-clause of course). Of course, the benefit of using pl/pgsql for this is that you could make the desired behaviour independent of client-implementations (which are all too likely to differ if there are multiple types of clients). However, for that to work reliably you will need to do some extra work to make sure it's the only possible code-path for inserting those records. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:921,4d6bf0d6235881485283256! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transactions and ID's generated by triggers
Hi, I have a situation that I dont know how to deal. I have 2 tables tdir_uris_files and tdir_uri_files_details. Please see the Image in attach. The table tdir_uris_files have the field id_ordinal that is originated by a trigger(before insert) The table tdir_uri_files_details use the field id_ordinal generated by trigger on the insert on the table tdir_uris_files Now my doubt. It is possible to do a transaction to this two tables at the same time? How can I know the value of the field id_ordinal that was generated by the trigger? Can I be able to do this? [code] BEGIN; INSERT INTO tdir_uris_files (uri, id_language, id_category, id_file_context, id_encode_format, n_file, file) values (...) INSERT INTO tdir_uri_files_details (uri, id_language, id_category, id_file_context, id_ordinal, id_file_type, id_file_detail, value) values (...); COMMIT; [/code] PS: Sorry my bad english. Best Regards, attachment: uri_files.PNG -- 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] Schema version control
Hi, Where can we donwload dbsteward? Best Regards, On Fri, Feb 11, 2011 at 5:16 AM, Bill Moran wmo...@potentialtech.com wrote: In response to Glenn Maynard gl...@zewt.org: On Thu, Feb 10, 2011 at 6:44 PM, Bill Moran wmo...@potentialtech.comwrote: dbsteward can do downgrades ... you just feed it the old schema and the new schema in reverse of how you'd do an upgrade ;) Oh, also, it allows us to do installation-specific overrides. We use this ONLY for DML for lookup lists where some clients have slightly different names for things than others. In theory, it could do DDL overrides as well, but we decided on a policy of not utilizing that because we wanted the schemas to be consistent on all our installs. What about upgrades that can't be derived directly from an inspection of the schema? Some examples: - Adding a NOT NULL constraint (without adding a DEFAULT). You often want to precede this with filling in any existing NULL values, so the new constraint doesn't fail. - Updating triggers, functions and their effects. For example, when I have an FTS index with a trigger to update an index column, and I change the underlying trigger, I often do something like UPDATE table SET column = column, to cause all of the update triggers to fire and recalculate the index columns. - Creating a new column based on an old one, and removing the old one; eg. add a column n, run UPDATE ... SET n = i*j * 2, and then drop the old columns i and j. - Updating data from an external source, such as ORM model code; for example, if you have a table representing external files, an update may want to calculate and update the SHA-1 of each file. - For efficiency, dropping a specific index while making a large update, and then recreating the index. In my experience, while generating schema updates automatically is handy, it tends to make nontrivial database updates more complicated. These sorts of things happen often and are an integral part of a database update, so I'm just curious how/if you deal with them. I've used Ruby's migrations, and for my Django databases I use my own migration system which is based in principle off of it: create scripts to migrate the database from version X to X+1 and X-1, and upgrade or downgrade by running the appropriate scripts in sequence. It's not ideal, since it can't generate a database at a specific version directly; it always has to run through the entire sequence of migrations to the version you want, and the migrations accumulate. However, it can handle whatever arbitrary steps are needed to update a database, and I don't need to test updates from every version to every other version. You're correct (based on our experience over the past few years). The big caveat is that 99.9% of the database changes don't fall into those nontrivial categories, and dbsteward makes those 99.9% of the changes easy to do, reliable to reproduce, and easy to track. We've added some stuff to handle the other .1% as well, like beforeUpdateSQL and afterUpdateSQL where you can put an arbitrary SQL strings to be run before or after the remainder of the automatic stuff is done. We probably haven't seen every circumstance that needs a special handling, but we've already struggled through a bunch. All this is part of the reason we're pushing to get this stuff open- sourced. We feel like we've got something that's pretty far along, and we feel that community involvement will help enhance things. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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
[GENERAL] Slow Inserts, two different scenarios.
Hi,
I have a problem when doing INSERT's in a table.
The table structure is:
uri (varchar 1) PK
id_language (varchar 10) PK
id_category (int4) PK
id_data (varchar 50) PK
id_ordinal (int4) PK (this field have a trigger to auto increment)
n_text (text)
When I run this function to do 90 INSERT's it runs well and in few time:
CREATE OR REPLACE FUNCTION doInserts() RETURNS integer AS
$BODY$
DECLARE
i integer;
BEGIN
i := 1;
while i = 90 loop
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'pagetitle', 'Pagina teste ' || i);
i := i + 1;
end loop;
RETURN i;
END
$BODY$
LANGUAGE 'plpgsql' ;
But when I do this with 10 INSERT's it seems to never end the
INSERT's operation, It is running at 5h now...
CREATE OR REPLACE FUNCTION doInserts() RETURNS integer AS
$BODY$
DECLARE
i integer;
BEGIN
i := 1;
while i = 10 loop
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'pagetitle', 'Pagina teste ' || i);
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'country_ad', 'italy');
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'services_available', 'service 1');
insert into tdir_uris_text (uri, id_language, id_category,
id_data,
n_text) values ('http://localhos/teste' || i, 'PORT', '2',
'services_available', 'service 2');
i := i + 1;
end loop;
RETURN i;
END
$BODY$
LANGUAGE 'plpgsql' ;
What could be the problem here? Any clues?
Best Regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database Design - Which design should I use? Two options.
Hi, I'm designing a database, but I'm with some doubts in the design. I have posted a question in stackoverflow because of the use of images. Can someone give some clues about which design should I use? The link to the question is here: http://stackoverflow.com/questions/4909105/database-design-which-design-should-i-use-two-options Best Regards, André. -- 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] Store base64 in database. Use bytea or text?
Hi,
Another question about this subject.
It is possible to cache this images from the database? In the future I
will need to cache the pictures.
If you have any knowledge about this, please give me a clue.
Best Regards,
On Wed, Jan 26, 2011 at 2:09 PM, Bill Moran wmo...@potentialtech.com wrote:
In response to Dmitriy Igrishin dmit...@gmail.com:
2011/1/26 Andre Lopes lopes80an...@gmail.com
Thanks for the reply.
I will mainly store files with 100kb to 250kb not bigger than this.
PQescapeByteaConn is not available in a default installation of
PostgreSQL? My hosting account hava a standard installation of
PostgreSQL. There are other options for escaping binary files?
Best Regards,
PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));
where dat column of table img of type bytea.
More specifically:
$query = INSERT INTO image_data (bytea_field) VALUES (' .
pg_escape_bytea($binary_data) . ');
pg_query($query);
And to get it back out:
$query = SELECT bytea_field FROM image_data;
$rs = pg_query($query);
$row = pg_fetch_assoc($rs);
$binary_data = pg_unescape_bytea($row['bytea_field']);
(note that I may have omitted some steps for clarity)
DO NOT use parametrized queries with PHP and bytea (I hate to say that,
because parametrized fields are usually a very good idea). PHP has a
bug that mangles bytea data when pushed through parametrized fields.
PHP bug #35800
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Store base64 in database. Use bytea or text?
Hi, I need to put some images on Base64 in a PostgreSQL database. Wich type should I use and what is the difference between using bytea or text to store Base64? Best Regards, -- 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] Store base64 in database. Use bytea or text?
Thanks for all the reply's. I will be using PHP for now to insert data. So I shouldn't use base64 to store images or any other kind of files. I'm new to storing files in the database. This will be my first experience. I will research about PQescapeByteaConn. Thanks for the help. Best Regards, On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran wmo...@potentialtech.com wrote: In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com wrote: Hi, I need to put some images on Base64 in a PostgreSQL database. Wich type should I use and what is the difference between using bytea or text to store Base64? I really don't think you want to do that. Base64 is used to make binary data 7-bit safe for compatibility with legacy systems (i.e. to embed arbitrary binary data within ASCII). Sometimes people escape binary data as base64 to store it in their DB, but they typically store it as bytea. Base64 probably isn't even a particularly good choice for escaping binary, let alone storing it. You should just use a generic escaping function. libpq has PQescapeByteaConn(), for example. A warning: last I checked, PHP's pg_escape_bytea() was broken, so be cautious if you're using PHP. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Store base64 in database. Use bytea or text?
Thanks for the reply. I will mainly store files with 100kb to 250kb not bigger than this. PQescapeByteaConn is not available in a default installation of PostgreSQL? My hosting account hava a standard installation of PostgreSQL. There are other options for escaping binary files? Best Regards, On Tue, Jan 25, 2011 at 10:58 PM, Dmitriy Igrishin dmit...@gmail.com wrote: 2011/1/26 Andre Lopes lopes80an...@gmail.com Thanks for all the reply's. I will be using PHP for now to insert data. So I shouldn't use base64 to store images or any other kind of files. I'm new to storing files in the database. This will be my first experience. You may want to use large objects to store files instead of using bytea data type. With large objects you can achieve streaming data transfer and as of PostgreSQL 9.0 it is possible to control access permissions on large objects via GRANT. But I don't know about support of large objects in PHP. I will research about PQescapeByteaConn. Thanks for the help. Best Regards, On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran wmo...@potentialtech.com wrote: In response to Peter Geoghegan peter.geoghega...@gmail.com: On 25 January 2011 22:27, Andre Lopes lopes80an...@gmail.com wrote: Hi, I need to put some images on Base64 in a PostgreSQL database. Wich type should I use and what is the difference between using bytea or text to store Base64? I really don't think you want to do that. Base64 is used to make binary data 7-bit safe for compatibility with legacy systems (i.e. to embed arbitrary binary data within ASCII). Sometimes people escape binary data as base64 to store it in their DB, but they typically store it as bytea. Base64 probably isn't even a particularly good choice for escaping binary, let alone storing it. You should just use a generic escaping function. libpq has PQescapeByteaConn(), for example. A warning: last I checked, PHP's pg_escape_bytea() was broken, so be cautious if you're using PHP. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need advise for database structure for non linear data.
Hi, I need advise about a database structure. I need to capture data from the web about one specific subject on few specific websites and insert that data to a database. I have done this question here before, but I think I have not explained very well. The problem with this task is that the information is not linear, if I try to design tables with fields for all possible data I will end up with many row fields with NULL values. There are any problem with this(end up with many row fields with NULL values)? Or should I user other kind of structure? For example store the data in one field and that field containing an associative array with data. What I mean with non linear data is the following: array( 'name' = 'Don', 'age' = '31' ); array( 'name' = 'Peter', 'age' = '28', 'car' = 'ford', 'km' = '2000' ); In a specific website search I will store only name and age, and in other website I will store name, age, car and km. I don't know If I explain weel my problem. My english is not very good. Best Regards.
Re: [GENERAL] Need advise for database structure for non linear data.
Hi, Thanks for the reply's. I was tempted to accept the Rodoslaw Smogura proposal. There will be about 100 websites to capture data on daily basis. Each website adds per day(average) 2 articles. Thomas talked about the noSQL possibility. What do you think would be better? I have no experience in noSQL and that could be a weakness. Best Regards, André On Mon, Jan 3, 2011 at 11:58 AM, Thomas Schmidt postg...@stephan.homeunix.net wrote: Hello, Am 03.01.11 12:46, schrieb Radosław Smogura: I can propose you something like this: website(id int, url varchar); attr_def (id int, name varchar); attr_val (id int, def_id reference attr_def.id, website_id int references website.id, value varchar); If all of your attributes in website are single valued then you can remove id from attr_val and use PK from website_id, def_id. Depending on your needs one or many from following indexes: attr_val(value) - search for attributes with value; (...) Probably you will use 2nd or 3rd index. Example of search on website select d.name, v.value from attre_def d join attr_val v on (v.def_id = d.id) join website w on (v.website_id = w.id) where d.name = '' and w.url='http://somtehing' Imho its hard - (if not impossible) to recommand a specific database scheme (incl indexes) without knowing the applications taking plance behind it. Your schema is nice for specific querying, but might blow up if lots of data is stored in the database (joins, index-building might be time consuming). On the other hand, google put some effort into their BigTable http://en.wikipedia.org/wiki/BigTable for storing tons of data... Thus - it all depends on the usage :-) Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to deal with field on the database that stores variable array's?
Hi, I need to develop a database table that will aceept a variable array field. My doubt is how to deal with updates on the array field. How can I store the information of the array fields? There any examples on the Internet on how to deal with this subject? What I mean with variable array is: array( 'name' = 'Don', 'age' = '31' ); array( 'name' = 'Peter', 'age' = '28', 'car' = 'ford', 'km' = '2000' ); Best Regards,
[GENERAL] How to obtain the maximum value of a date, between 3 tables...
Hi, I need to obtain the maximum value of a date, but that comparison will be made between 3 tables... I will explain better with a query... [code] select a.last_refresh_date as d1, ae.last_refresh_date as d2, ha.last_refresh_date as d3 from tbl1 a join tbl2 ae on a.id_anuncio_externo = ae.id_anuncio_externo join tbl3 ha on a.id_anuncio_externo = ha.id_anuncio_externo [/code] My question is how is the best way to obtain with date is the greatest, d1, d2 or d3 Can you guys give me a clue. Best Regards,
Re: [GENERAL] How to obtain the maximum value of a date, between 3 tables...
Hi, Thanks for the reply. And there are other options to do it without using a UNION? I don't need to know from witch table comes the greatest date, but the query is complex, this query is part of an UNION. The use of the CASE WHEN could be an alternative? Best Regards, On Wed, Dec 8, 2010 at 1:20 PM, Jon Nelson jnelson+pg...@jamponi.netjnelson%2bpg...@jamponi.net wrote: On Wed, Dec 8, 2010 at 7:15 AM, Andre Lopes lopes80an...@gmail.com wrote: Hi, I need to obtain the maximum value of a date, but that comparison will be made between 3 tables... I will explain better with a query... [code] select a.last_refresh_date as d1, ae.last_refresh_date as d2, ha.last_refresh_date as d3 from tbl1 a join tbl2 ae on a.id_anuncio_externo = ae.id_anuncio_externo join tbl3 ha on a.id_anuncio_externo = ha.id_anuncio_externo [/code] My question is how is the best way to obtain with date is the greatest, d1, d2 or d3 If you don't need to know which table it came from I would probably try select a.last_refresh_date as d1, NULL as d2, NULL as d3 FROM tbl1 as a UNION ALL ... -- Jon
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
Hi,
Thanks for the reply.
I have write the transaction, but I have some doubt's... If in this example
the Update is executed successfully and the Function it is not, what
happens? The Update automatically rolls back?
Example:
[code]
Begin;
update aae_anuncios
set
n_anunciante = 'teste',
email = 'te...@email.com',
telefone_privado = '123456789',
dat_nasc = '1980-01-01',
n_anuncio = 'teste nome',
telefone_anuncio = '234567890',
id_genero = 'femi',
id_cidade = '1452003',
id_nacionalidade = 'BRAS',
id_orientacao = 'h'
where id_anuncio_externo = '38';
select apr_update_hist_2_1('aae_hist_anuncios',
'id_anuncio_externo',
'38', /* id_anuncio_externo */
'2010-08-31', /* data_inicio */
'2010-12-29', /* data_fim */
'AA' /* Motivo: Aprovação Anúncio */
);
commit;
[/code]
Best Regards,
On Sun, Nov 7, 2010 at 3:41 PM, Scott Ribe scott_r...@killerbytes.comwrote:
On Nov 7, 2010, at 8:37 AM, Andre Lopes wrote:
The only way I can guarantee a transaction is in a Function or there are
other ways?
http://www.postgresql.org/docs/9.0/interactive/tutorial-transactions.html
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] It is possible to update more than 1 table in the same update statement?
Hi, I need to update various tables in the same update. It is possible to do it? Best Regards,
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
Sorry for not explain well. I mean update more than one table at the same time with something like this: update table1, table2 set table1.f1 = 'aaa', table2.date = '2001-01-01' where table1.id = 'x1' and table2.id = 'x1' Something like this is possible? It is the best way to do it? Best Regards, On Sun, Nov 7, 2010 at 3:11 PM, Scott Ribe scott_r...@killerbytes.comwrote: On Nov 7, 2010, at 7:58 AM, Andre Lopes wrote: need to update various tables in the same update. It is possible to do it? Transactions??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
Re: [GENERAL] It is possible to update more than 1 table in the same update statement?
The only way I can guarantee a transaction is in a Function or there are other ways? Best Regards, On Sun, Nov 7, 2010 at 3:22 PM, Szymon Guz mabew...@gmail.com wrote: On 7 November 2010 16:20, Andre Lopes lopes80an...@gmail.com wrote: Sorry for not explain well. I mean update more than one table at the same time with something like this: update table1, table2 set table1.f1 = 'aaa', table2.date = '2001-01-01' where table1.id = 'x1' and table2.id = 'x1' Something like this is possible? It is the best way to do it? Best Regards, This is not possible, however you could do two updates in one transaction. regards Szymon
[GENERAL] Tools for form generation in PHP/HTML from database models/queries
Hi, I need to do some repetitive work in form creation to insert, update and delete data from the database. There are some tools that handle with form creation from a PostgreSQL database? PS: Sorry my English. Best Regards,
[GENERAL] How to dump only the the data without schema?
Hi, I need to generate the dump of a PostgreSQL database only with the data with INSERT's. It is possible to do this? Best Regards,
[GENERAL] How to use pg_restore with *.sql file?
Hi, I have generate an *.sql file with the command: [quote] pg_dump -a --inserts databasename exportfilename.sql [/quote] How can I use the pg_restore to import the data to the database? I have tried: [quote] pg_restore -d databasename exportfilename.sql [/quote] But without success... What is the best way to import the *.sql file to the database? Best Regards,
Re: [GENERAL] How to use pg_restore with *.sql file?
Thanks a lot! It works. On Sat, Sep 25, 2010 at 1:23 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Sat, Sep 25, 2010 at 6:19 AM, Andre Lopes lopes80an...@gmail.com wrote: Hi, I have generate an *.sql file with the command: [quote] pg_dump -a --inserts databasename exportfilename.sql [/quote] How can I use the pg_restore to import the data to the database? Don't. Just use psql with plain sql dumps: psql databasename -f exportfilename.sql
[GENERAL] How to add permissions to views?
Hi, I have developed a database system where every table have a view mapping the contents, so the users don't have direct access to the tables. For UPDATE and DELETE I have created RULES on the views. My question: I will create a user to give permissions to the views. I don't know what kind of permissions I should add. For example, for a view that have RULEs for UPDATE and DELETE wich kind of GRANT should I do? PS: Sorry for my bad english. Best Regards,
[GENERAL] How to import *.sql file to postgresql database
Hi, I have a DUMP file with INSERT's commands. I need to import this data to postgresql database with the psql command. How can I do this task? Best Regards,
Re: [GENERAL] How to import *.sql file to postgresql database
Thanks for the reply, I have this error: [quote] ERROR: invalid byte sequence for encoding UTF8: 0xff HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. [/quote] I have set the client encoding to LATIN1 like this, [quote] $psql database database= set client_encoding to LATIN1; SET database=\i /home/folder1/data.sql [/quote] Then I got this different error: [quote] psql:/home/folder1/data.sql:1: ERROR: syntax error at or near ÿþ LINE 1: ÿþ- ^ [/quote] How should I solve this problem? Best Regards, On Sun, Jul 18, 2010 at 9:10 PM, Szymon Guz mabew...@gmail.com wrote: 2010/7/18 Andre Lopes lopes80an...@gmail.com Hi, I have a DUMP file with INSERT's commands. I need to import this data to postgresql database with the psql command. How can I do this task? Best Regards, Hi, you can do that for example using the following command: psql database file.sql where datatabase is the name of the database you use. regards Szymon Guz
Re: [GENERAL] How to import *.sql file to postgresql database
If I do this command gives me the same error: [quote] ERROR: invalid byte sequence for encoding UTF8: 0xff HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. [/quote] What more can I do? Best Regards, On Sun, Jul 18, 2010 at 9:11 PM, Edoardo Panfili edoa...@aspix.it wrote: On 18/07/10 22.06, Andre Lopes wrote: Hi, I have a DUMP file with INSERT's commands. I need to import this data to postgresql database with the psql command. How can I do this task? I think psql -U user_name database_name dump_file EDoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to change the file encoding of a 3gb file?
Hi, I'am trying to import an SQL file with 3gb of INSERTS. I must to change the encode of the file to UTF8, how can I change the encode of the file without open it? This 3gb file crashes every program... Give me a clue. Best Regards,
[GENERAL] Rules in views, how to?
Hi, I'am using rules in views, but I'am not sure about how the rules work... let me explain... For example, I have this table: [code] CREATE TABLE atau_utilizadores ( id int4 NOT NULL, group_id int4 NOT NULL, ip_address char(16) NOT NULL, username varchar(50) NOT NULL, password varchar(40) NOT NULL, salt varchar(40), email varchar(40) NOT NULL, activation_code varchar(40), forgotten_password_code varchar(40), remember_code varchar(40), created_on timestamp NOT NULL, last_login timestamp, active int4, coment varchar(2000), id_utiliz_ins varchar(45), id_utiliz_upd varchar(45), data_ult_actual timestamp, PRIMARY KEY(id), CONSTRAINT check_id CHECK(id = 0), CONSTRAINT check_group_id CHECK(group_id = 0), CONSTRAINT check_active CHECK(active = 0) ); [/code] And I have also a view to this table with a rule do the user be able to do INSERTS in views: [code] CREATE OR REPLACE VIEW aau_utilizadores AS select * from atau_utilizadores; CREATE OR REPLACE RULE ins_aau_utilizadores AS ON INSERT TO aau_utilizadores DO INSTEAD (insert into atau_utilizadores (id, group_id, ip_address, username, password, salt, email, activation_code, forgotten_password_code, remember_code, created_on, last_login, active) values (NEW.id, NEW.group_id, NEW.ip_address, NEW.username, NEW.password, NEW.salt, NEW.email, null, null, null, NEW.created_on, null, NEW.active)); [/code] Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain... Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when WHERE email = 'X' or WHERE id = 'Y' . Question: How can I deal with this? The update rule should be: [code] update atau_utilizadores set group_id = NEW.group_id, password = NEW.password, salt = NEW.salt, email = NEW.email, activation_code = NEW.activation_code, forgotten_password_code = NEW.forgotten_password_code, remember_code = NEW.remember_code, created_on = NEW.created_on, last_login = NEW.last_login, active = NEW.active [/code] or [code] update atau_utilizadores set group_id = NEW.group_id, password = NEW.password, salt = NEW.salt, email = NEW.email, activation_code = NEW.activation_code, forgotten_password_code = NEW.forgotten_password_code, remember_code = NEW.remember_code, created_on = NEW.created_on, last_login = NEW.last_login, active = NEW.active where (email = OLD.email or id = OLD.id) ??? PS: Sorry for my bad english. Best Regards, André [/code]
[GENERAL] Rules in views, how to?
Hi, I'am using rules in views, but I'am not sure about how the rules work... let me explain... For example, I have this table: [code] CREATE TABLE atau_utilizadores ( id int4 NOT NULL, group_id int4 NOT NULL, ip_address char(16) NOT NULL, username varchar(50) NOT NULL, password varchar(40) NOT NULL, salt varchar(40), email varchar(40) NOT NULL, activation_code varchar(40), forgotten_password_code varchar(40), remember_code varchar(40), created_on timestamp NOT NULL, last_login timestamp, active int4, coment varchar(2000), id_utiliz_ins varchar(45), id_utiliz_upd varchar(45), data_ult_actual timestamp, PRIMARY KEY(id), CONSTRAINT check_id CHECK(id = 0), CONSTRAINT check_group_id CHECK(group_id = 0), CONSTRAINT check_active CHECK(active = 0) ); [/code] And I have also a view to this table with a rule do the user be able to do INSERTS in views: [code] CREATE OR REPLACE VIEW aau_utilizadores AS select * from atau_utilizadores; CREATE OR REPLACE RULE ins_aau_utilizadores AS ON INSERT TO aau_utilizadores DO INSTEAD (insert into atau_utilizadores (id, group_id, ip_address, username, password, salt, email, activation_code, forgotten_password_code, remember_code, created_on, last_login, active) values (NEW.id, NEW.group_id, NEW.ip_address, NEW.username, NEW.password, NEW.salt, NEW.email, null, null, null, NEW.created_on, null, NEW.active)); [/code] Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain... Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when WHERE email = 'X' or WHERE id = 'Y' . Question: How can I deal with this? The update rule should be: [code] update atau_utilizadores set group_id = NEW.group_id, password = NEW.password, salt = NEW.salt, email = NEW.email, activation_code = NEW.activation_code, forgotten_password_code = NEW.forgotten_password_code, remember_code = NEW.remember_code, created_on = NEW.created_on, last_login = NEW.last_login, active = NEW.active [/code] or [code] update atau_utilizadores set group_id = NEW.group_id, password = NEW.password, salt = NEW.salt, email = NEW.email, activation_code = NEW.activation_code, forgotten_password_code = NEW.forgotten_password_code, remember_code = NEW.remember_code, created_on = NEW.created_on, last_login = NEW.last_login, active = NEW.active where (email = OLD.email or id = OLD.id) [/code] ??? PS: Sorry for my bad english. Best Regards, André
Re: [GENERAL] Rules in views, how to?
Hi Alban, But in my application I have more than one way of uniquely identify the record. Could be by the email field or by the id field. Thera are update that are done by the WHERE email clause and other by the WHERE id clause. It is possible to deal with this? Best Regards, On Tue, Jul 6, 2010 at 11:54 AM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 6 Jul 2010, at 12:28, Andre Lopes wrote: Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain... Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when WHERE email = 'X' or WHERE id = 'Y' . Question: How can I deal with this? In the WHERE-clause you use the columns from the OLD record that uniquely identify that record. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:921,4c330b7e286211912975436!
Re: [GENERAL] [SOLVED] Rules in views, how to?
Ok, I have done the UPDATE RULE like this and works! [code] update atau_utilizadores set group_id = NEW.group_id, password = NEW.password, salt = NEW.salt, email = NEW.email, activation_code = NEW.activation_code, forgotten_password_code = NEW.forgotten_password_code, remember_code = NEW.remember_code, created_on = NEW.created_on, last_login = NEW.last_login, active = NEW.active where (id = OLD.id or username = OLD.username or email = OLD.email) [/code] Best Regards, On Tue, Jul 6, 2010 at 12:03 PM, Andre Lopes lopes80an...@gmail.com wrote: Hi Alban, But in my application I have more than one way of uniquely identify the record. Could be by the email field or by the id field. Thera are update that are done by the WHERE email clause and other by the WHERE id clause. It is possible to deal with this? Best Regards, On Tue, Jul 6, 2010 at 11:54 AM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 6 Jul 2010, at 12:28, Andre Lopes wrote: Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain... Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when WHERE email = 'X' or WHERE id = 'Y' . Question: How can I deal with this? In the WHERE-clause you use the columns from the OLD record that uniquely identify that record. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:921,4c330b7e286211912975436!
Re: [GENERAL] [SOLVED] Rules in views, how to?
Hi, Thanks for the reply. In the application there are two kinds of UPDATES to this table. [code] update aau_utilizadores set group_id = 3 where email = pEMAIL; [/code] and [code] update aau_utilizadores set password = 3 where id = pNEWPASSWORD; [/code] If I use the clause WHERE only in id will not work fot both cases, or will work? Best Regards, On Tue, Jul 6, 2010 at 12:46 PM, Sam Mason s...@samason.me.uk wrote: On Tue, Jul 06, 2010 at 12:28:35PM +0100, Andre Lopes wrote: Ok, I have done the UPDATE RULE like this and works! where (id = OLD.id or username = OLD.username or email = OLD.email) I'm pretty sure you just want to be using the id column above. Using an OR expression as you're doing could have some strange side effects. You may also want to consider a UNIQUE constraint on the username (and maybe email) fields as well, especially as you've said they should be able to be used to uniquely determine a user. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to know if an INSERT is done inside a function?
Hi, I have a function, at the end I need to know if the INSERTS have run successfully or not. Here is the function: [code] CREATE OR REPLACE FUNCTION apr_insert_utilizador_ap (IN ppgroup_id int4, IN ppip_address char, IN ppusername varchar, IN pppassword varchar, IN ppemail varchar, IN ppactive int4, IN ppnome_real varchar, IN pptelefone_pessoal varchar, IN ppid_anunciante varchar, OUT ppreturn_value int4 ) RETURNS int4 AS $BODY$ DECLARE pGROUP_ID alias for $1; pIP_ADDRESSalias for $2; pUSERNAMEalias for $3; pPASSWORDalias for $4; pEMAILalias for $5; pACTIVEalias for $6; pNOME_REALalias for $7; pTELEFONE_PESSOALalias for $8; pID_ANUNCIANTEalias for $9; vID_UTILIZADOR_MAXint4; vID_UTILIZADOR_NOVOint4; vRETURNint4; BEGIN SELECT INTO vID_UTILIZADOR_MAX max(id) AS max_id FROM aau_utilizadores; vID_UTILIZADOR_NOVO := vID_UTILIZADOR_MAX + 1; INSERT INTO aau_utilizadores (id, group_id, ip_address, username, password, salt, email, activation_code, forgotten_password_code, remember_code, created_on, last_login, active) VALUES (vID_UTILIZADOR_NOVO, pGROUP_ID, pIP_ADDRESS, pUSERNAME, pPASSWORD, null, pEMAIL, null, null, null, NOW(), null, pACTIVE); INSERT INTO aau_metadata (id, user_id, nome_real, telefone_pessoal) VALUES (vID_UTILIZADOR_NOVO, vID_UTILIZADOR_NOVO, pNOME_REAL, pTELEFONE_PESSOAL); INSERT INTO aau_anunciantes (user_id, id_anunciante) VALUES (vID_UTILIZADOR_NOVO, pID_ANUNCIANTE); vRETURN := 1; ppreturn_value := vRETURN; END; $BODY$ LANGUAGE PLpgSQL RETURNS NULL ON NULL INPUT VOLATILE EXTERNAL SECURITY INVOKER; [/code] If the INSERTS are all done the function RETURNS 1, but how can I RETURN 0 if any error occurred? Best Regards, André.
Re: [GENERAL] How to know if an INSERT is done inside a function?
Hi, Thanks for your reply. Yes, in the Postgre command line I see the exception, the problem is that I'am using this function in a PHP code. I need send the value 1 to the OUT parameter if the function is successful or send the value 0 to the OUT parameter if the function not runs successful. How can I do this? Best Regards, On Sun, Jul 4, 2010 at 11:05 AM, Pavel Stehule pavel.steh...@gmail.comwrote: Hello every unsuccessful SQL command raises exception in PL/pgSQL. So if your function is finished without exception, then INSERT is ok. Regards Pavel Stehule 2010/7/4 Andre Lopes lopes80an...@gmail.com: Hi, I have a function, at the end I need to know if the INSERTS have run successfully or not. Here is the function: [code] CREATE OR REPLACE FUNCTION apr_insert_utilizador_ap (IN ppgroup_id int4, IN ppip_address char, IN ppusername varchar, IN pppassword varchar, IN ppemail varchar, IN ppactive int4, IN ppnome_real varchar, IN pptelefone_pessoal varchar, IN ppid_anunciante varchar, OUT ppreturn_value int4 ) RETURNS int4 AS $BODY$ DECLARE pGROUP_ID alias for $1; pIP_ADDRESSalias for $2; pUSERNAMEalias for $3; pPASSWORDalias for $4; pEMAILalias for $5; pACTIVEalias for $6; pNOME_REALalias for $7; pTELEFONE_PESSOALalias for $8; pID_ANUNCIANTEalias for $9; vID_UTILIZADOR_MAXint4; vID_UTILIZADOR_NOVOint4; vRETURNint4; BEGIN SELECT INTO vID_UTILIZADOR_MAX max(id) AS max_id FROM aau_utilizadores; vID_UTILIZADOR_NOVO := vID_UTILIZADOR_MAX + 1; INSERT INTO aau_utilizadores (id, group_id, ip_address, username, password, salt, email, activation_code, forgotten_password_code, remember_code, created_on, last_login, active) VALUES (vID_UTILIZADOR_NOVO, pGROUP_ID, pIP_ADDRESS, pUSERNAME, pPASSWORD, null, pEMAIL, null, null, null, NOW(), null, pACTIVE); INSERT INTO aau_metadata (id, user_id, nome_real, telefone_pessoal) VALUES (vID_UTILIZADOR_NOVO, vID_UTILIZADOR_NOVO, pNOME_REAL, pTELEFONE_PESSOAL); INSERT INTO aau_anunciantes (user_id, id_anunciante) VALUES (vID_UTILIZADOR_NOVO, pID_ANUNCIANTE); vRETURN := 1; ppreturn_value := vRETURN; END; $BODY$ LANGUAGE PLpgSQL RETURNS NULL ON NULL INPUT VOLATILE EXTERNAL SECURITY INVOKER; [/code] If the INSERTS are all done the function RETURNS 1, but how can I RETURN 0 if any error occurred? Best Regards, André.
Re: [GENERAL] How to know if an INSERT is done inside a function?
Great! That is what I need! Thank you Pavel. Best Regards, On Sun, Jul 4, 2010 at 12:20 PM, Pavel Stehule pavel.steh...@gmail.comwrote: 2010/7/4 Andre Lopes lopes80an...@gmail.com: Hi, Thanks for your reply. Yes, in the Postgre command line I see the exception, the problem is that I'am using this function in a PHP code. I need send the value 1 to the OUT parameter if the function is successful or send the value 0 to the OUT parameter if the function not runs successful. How can I do this? CREATE OR REPLACE FUNCTION foo(...) RETURNS int AS $$ BEGIN INSERT INTO ... RETURN 1 EXCEPTION WHEN OTHERS THEN RETURN 0 END $$ LANGUAGE plpgsql; But I am sure so you can see exception from php too. Regards Pavel Best Regards, On Sun, Jul 4, 2010 at 11:05 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello every unsuccessful SQL command raises exception in PL/pgSQL. So if your function is finished without exception, then INSERT is ok. Regards Pavel Stehule 2010/7/4 Andre Lopes lopes80an...@gmail.com: Hi, I have a function, at the end I need to know if the INSERTS have run successfully or not. Here is the function: [code] CREATE OR REPLACE FUNCTION apr_insert_utilizador_ap (IN ppgroup_id int4, IN ppip_address char, IN ppusername varchar, IN pppassword varchar, IN ppemail varchar, IN ppactive int4, IN ppnome_real varchar, IN pptelefone_pessoal varchar, IN ppid_anunciante varchar, OUT ppreturn_value int4 ) RETURNS int4 AS $BODY$ DECLARE pGROUP_ID alias for $1; pIP_ADDRESSalias for $2; pUSERNAMEalias for $3; pPASSWORDalias for $4; pEMAILalias for $5; pACTIVEalias for $6; pNOME_REALalias for $7; pTELEFONE_PESSOALalias for $8; pID_ANUNCIANTEalias for $9; vID_UTILIZADOR_MAXint4; vID_UTILIZADOR_NOVOint4; vRETURNint4; BEGIN SELECT INTO vID_UTILIZADOR_MAX max(id) AS max_id FROM aau_utilizadores; vID_UTILIZADOR_NOVO := vID_UTILIZADOR_MAX + 1; INSERT INTO aau_utilizadores (id, group_id, ip_address, username, password, salt, email, activation_code, forgotten_password_code, remember_code, created_on, last_login, active) VALUES (vID_UTILIZADOR_NOVO, pGROUP_ID, pIP_ADDRESS, pUSERNAME, pPASSWORD, null, pEMAIL, null, null, null, NOW(), null, pACTIVE); INSERT INTO aau_metadata (id, user_id, nome_real, telefone_pessoal) VALUES (vID_UTILIZADOR_NOVO, vID_UTILIZADOR_NOVO, pNOME_REAL, pTELEFONE_PESSOAL); INSERT INTO aau_anunciantes (user_id, id_anunciante) VALUES (vID_UTILIZADOR_NOVO, pID_ANUNCIANTE); vRETURN := 1; ppreturn_value := vRETURN; END; $BODY$ LANGUAGE PLpgSQL RETURNS NULL ON NULL INPUT VOLATILE EXTERNAL SECURITY INVOKER; [/code] If the INSERTS are all done the function RETURNS 1, but how can I RETURN 0 if any error occurred? Best Regards, André.
[GENERAL] Open Source Forum Software using PostgreSQL?
Hi, I need to use an Forum Software. There is any Open Souce Forum Script using PostgreSQL? Best Regards, André.
Re: [GENERAL] How to know if an INSERT is done inside a function?
Hi, The function will run in the php-cli, in a CronJob, it is not for use in a PHP webpage. I think doesn't matter the extra time that takes to run. Best Regards, On Sun, Jul 4, 2010 at 4:11 PM, Pavel Stehule pavel.steh...@gmail.comwrote: 2010/7/4 Merlin Moncure mmonc...@gmail.com: On Sun, Jul 4, 2010 at 7:25 AM, Andre Lopes lopes80an...@gmail.com wrote: Great! That [begin exception end] is what I need! Thank you Pavel. Best Regards, Just a quick heads up: functions with exception handlers tend to be more expensive than those without, even if the exception doesn't fire. yes, better to use prepared statement and read diagnostics info from PHP Pavel merlin
[GENERAL] How to emulate password generation in PHP with PlpgSQL?
Hi,
I need to create users in a database function. I'am dealing with a PHP
application, the code that generate the password is this:
[code]
public function salt()
{
return substr(md5(uniqid(rand(), true)), 0, 10);
}
public function hash_password($password, $salt=false)
{
if (empty($password))
{
return FALSE;
}
if (FALSE $salt)
{
return sha1($password . $salt);
}
else
{
$salt = $this-salt();
return $salt . substr(sha1($salt . $password), 0, -10);
}
}
[/code]
It is possible to emulate this in a PlpgSQL function?
I have a function that generates the SHA1 codes
[code]
CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
[/code]
But I'am not getting how to generate the SALT. Can someone give me a clue on
how to do this.
Best Regards,
[GENERAL] Hosting without pgcrypto functions. There are other solutions?
Hi,
I have an account in A2Hosting.com, and I'm developing some functions that
deal with encryption.
A2Hosting.com don't have available the function digest()
[code]
ERROR: function digest(unknown, unknown) does not exist
LINE 1: select digest('', 'sha1')
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
Indicação de entrada :
select digest('', 'sha1')
[/code]
I need to compile a SHA1 function, but without the digest() function,
nothing done...
[code]
CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
[/code]
There is a way to get this job done without the digest() function?
I need also to be able to do this select select substr(gen_salt('md5'), 0,
10) but there is no gen_salt() available...
What is my best option? To change hosting account? There is some hosting
accounts with this functions available in the Postgre?
Best Regards,
[GENERAL] How to return an INT4 subtracting dates?
Hi, I need to return an int4 subtracting two dates, but returns me an interval. select end_date - now() as interger_number from hist_anuncios How to return an integer out of this? Best Regards,
[GENERAL] How to return an Int4 when subtracting dates/timestamps
Hi, I need to return an Int4 when I do this king of select [code] select CURRENT_DATE - '2009-12-31' from tbl_sometable [/code] This select returns an Interval. How can I return an Integer? Like '138' Sorry my bad english. Best Regards,
[GENERAL] Shell script to Backup/Dump Database
Hi, I'am looking fo a shell script do Backup/Dump my PostgreSQL twice a day. I will need to do a full dump (schema + data), schema dump (only schema) and data (only data) There is something done on this subject? I'am very poor in shell script, there are some scripts on the web ready to use? Best Regards,
Re: [GENERAL] Shell script to Backup/Dump Database
Thanks for the reply. I will test today. There are some good practices that I should do? For example, I have this database in a shared hosting, should I add the functionality of send the dumps by FTP to my PC once a week? Give some more ideias to avoid data loss in case of disaster. Best Regards, On Wed, May 12, 2010 at 4:45 PM, Thom Brown t...@linux.com wrote: On 12 May 2010 16:30, Andre Lopes lopes80an...@gmail.com wrote: Hi, I'am looking fo a shell script do Backup/Dump my PostgreSQL twice a day. I will need to do a full dump (schema + data), schema dump (only schema) and data (only data) There is something done on this subject? I'am very poor in shell script, there are some scripts on the web ready to use? Best Regards, Yes, several of us discussed something like this recently. Are you looking to back up all databases on the cluster or just an individual database? If it's a cluster, you could have a shell script like this: #!/bin/bash mkdir /tmp/`date +\%Y\%m\%d` pg_dumpall -U postgres | gzip /tmp/`date +\%Y\%m\%d`/FULL.sql.gz pg_dumpall -c -U postgres | gzip /tmp/`date +\%Y\%m\%d`/SCHEMA.sql.gz pg_dumpall -a -U postgres | gzip /tmp/`date +\%Y\%m\%d`/DATA.sql.gz If you want a single database, try this: #!/bin/bash mkdir /tmp/`date +\%Y\%m\%d` pg_dump -U postgres my_database | gzip /tmp/`date +\%Y\%m\%d`/my_database_FULL.sql.gz pg_dump -s -U postgres my_database | gzip /tmp/`date +\%Y\%m\%d`/my_database_SCHEMA.sql.gz pg_dump -a -U postgres my_database | gzip /tmp/`date +\%Y\%m\%d`/my_database_DATA.sql.gz If you want each individual database, try this: #!/bin/bash mkdir /tmp/`date +\%Y\%m\%d` query=select datname from pg_database where not datistemplate and datallowconn; for line in `psql -U postgres -At -c $query postgres` do pg_dump -U postgres $line | gzip /tmp/`date +\%Y\%m\%d`/$line_FULL.sql.gz pg_dump -s -U postgres $line | gzip /tmp/`date +\%Y\%m\%d`/$line_SCHEMA.sql.gz pg_dump -a -U postgres $line | gzip /tmp/`date +\%Y\%m\%d`/$line_DATA.sql.gz done Obviously you can change the output location to something other that /tmp if you're not going to transfer it anywhere. Just make sure the path exists. If you are copying it away immediately after, make sure you delete it or they'll build up. If you want to schedule it, bung it in a shell script like backup_databases.sh and stick it in the root crontab. Regards Thom
[GENERAL] How to deal with NULL values on dynamic queries?
Hi,
I have a query that some values could be NULL, how can I deal with this
problem?
[code]
EXECUTE 'INSERT INTO '
|| quote_ident(pNOME_VIEW)
|| '('
|| quote_ident(pCHAVE_1)
|| ', DAT_INICIO, DAT_FIM, COMENT) values ('
|| quote_literal(pVAL_CHAVE_1)
|| ', '
|| quote_literal(pVAL_CHAVE_2)
|| ', '
|| quote_literal(pVAL_CAMPO1)
|| ', '
|| quote_literal(pVAL_COMENT)
|| ')';
[/code]
The variable pVAL_COMENT could be NULL or have a value. How can I deal with
this?
Sorry the bad english.
Best Regards,
Re: [GENERAL] Function not RAISE NOTICE if a parameter is NULL
Oh... Thanks. What a lame I'm. Best Regards, On Wed, May 5, 2010 at 1:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andre Lopes lopes80an...@gmail.com writes: Thanks for the reply. I have not call it STRICT... Yes you are: RETURNS NULL ON NULL INPUT regards, tom lane
Re: [GENERAL] Dynamic SQL with pgsql, how to?
Thanks for the reply, It is working now. Best Regards. On Tue, May 4, 2010 at 7:44 AM, Maximilian Tyrtania maximilian.tyrta...@byte-employer.de wrote: Am 03.05.2010 um 23:50 schrieb Andre Lopes: Thanks for the reply's, I need to do a Dynamic SELECT INTO. There is a way of doing it? Yes. Plpgsql supports this: EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ]; See http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html Max Maximilian Tyrtania Software-Entwicklung Dessauer Str. 6-7 10969 Berlin Tel.:++49/30/48827-952 Mobil: 0152/292 707 36 email: maximilian.tyrta...@byte-employer.de
[GENERAL] How to exit/abort from a function that returns VOID?
HI, I have a PLPgSQL function that return void but I need to exit the function if some condition returns true. I have tried to do this, but I got an error: [code] IF pVAL_CHAVE_2 pVAL_CAMPO1 THEN RAISE NOTICE 'O campo data fim tem de ser maior que o campo data inicio.'; RETURN 0; END IF; [/code] The error is the folowing: [quote] ERROR: RETURN cannot have a parameter in function returning void at or near 0 at character 1973 [/quote] My question. How can I display the NOTICE and exit the function? Best Regards,
Re: [GENERAL] How to exit/abort from a function that returns VOID?
Thanks. I got working. Best Regards, On Tue, May 4, 2010 at 10:25 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On Tuesday 04 May 2010 1:55:35 pm Andre Lopes wrote: HI, I have a PLPgSQL function that return void but I need to exit the function if some condition returns true. I have tried to do this, but I got an error: [code] IF pVAL_CHAVE_2 pVAL_CAMPO1 THEN RAISE NOTICE 'O campo data fim tem de ser maior que o campo data inicio.'; RETURN 0; END IF; [/code] The error is the folowing: [quote] ERROR: RETURN cannot have a parameter in function returning void at or near 0 at character 1973 [/quote] My question. How can I display the NOTICE and exit the function? Best Regards, RETURN; From here: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING -- Adrian Klaver adrian.kla...@gmail.com
[GENERAL] Function not RAISE NOTICE if a parameter is NULL
Hi,
I have a function, that I'm debugging...
If I use NULL in any parameter the function does not RAISE any NOTICE. I
can't pass NULL values in parameters?
[code]
select apr_ins_gder_2_1
('table_fgh' /* Varchar */,
'id_fgh' /* Varchar */,
'5' /* Varchar */,
NULL /* Date */,
'2001-01-05' /* Date */,
'coment...' /* Varchar */)
[/code]
Solutions for pass NULL values in the parameters and receive RAISE NOTICE's?
Sorry for my english.
Best Regards,
Re: [GENERAL] Function not RAISE NOTICE if a parameter is NULL
Hi,
Thanks for the reply. I have not call it STRICT...
The function is:
[code]
CREATE OR REPLACE FUNCTION apr_insert_hist_2_1 (IN pNOME_VIEW varchar, IN
pCHAVE_1 varchar, IN pVAL_CHAVE_1 varchar, IN pVAL_CHAVE_2 date, IN
pVAL_CAMPO1 date, IN pVAL_COMENT varchar)
RETURNS void AS
$BODY$
DECLARE
pNOME_VIEW alias for $1;
pCHAVE_1 alias for $2;
pVAL_CHAVE_1 alias for $3;
pVAL_CHAVE_2alias for $4;
pVAL_CAMPO_1alias for $5;
pVAL_COMENT alias for $6;
vQUERY_COUNTvarchar;
vCONTAint4;
vDAT_INICIO_0date;
vDAT_FIM_0date;
vMAX_DAT_INICIO_MAI_0 date;
-- vEMAIL_KEYvarchar;
vDEBUG varchar;
vDEBUG_2varchar;
BEGIN
-- Condições para se abortar a função
-- Verificar que a dat_inicio está preenchida, não pode estar a nulo.
IF pVAL_CHAVE_2 ISNULL THEN
RAISE NOTICE 'O campo data inicio não pode ser nulo.';
RETURN; /* Sai da função */
END IF;
-- Verificar que a dat_fim é maior que a dat_inicio.
IF pVAL_CHAVE_2 pVAL_CAMPO1 THEN
RAISE NOTICE 'O campo data fim tem de ser maior que o campo data
inicio.';
RETURN; /* Sai da função */
END IF;
-- Vou verificar se já existe algum registo para o ID_ANUNCIO_EXTERNO
EXECUTE'SELECT COUNT(*) as conta FROM '
|| quote_ident(pNOME_VIEW)
|| ' WHERE '
|| quote_ident(pCHAVE_1)
|| ' = '
|| quote_literal(pVAL_CHAVE_1)
INTO STRICT vCONTA;
-- Para DEBUG
-- RAISE NOTICE 'Contagem: %', vCONTA;
-- Verificar se existem registos na tabela
-- Se não existirem registos, entra aqui.
IF vCONTA = 0 THEN
RAISE NOTICE 'entrou aqui 0';
-- Fazer o INSERT dinamico como primeiro registo do ID_QQ_COISA
EXECUTE 'INSERT INTO '
|| quote_ident(pNOME_VIEW)
|| ' ('
|| quote_ident(pCHAVE_1)
|| ', DAT_INICIO, DAT_FIM, COMENT) values ('
|| quote_literal(pVAL_CHAVE_1)
|| ', '
|| quote_literal(pVAL_CHAVE_2)
|| ', '
|| quote_literal(pVAL_CAMPO1)
|| ', '
|| quote_literal(pVAL_COMENT)
|| ')';
RAISE NOTICE 'dat_inicio: %', vDAT_INICIO_0;
RAISE NOTICE 'dat_fim: %', vDAT_FIM_0;
-- RAISE NOTICE 'select: %', vDEBUG;
-- Se já existirem registo, entra aqui
ELSEIF vCONTA 0 THEN
RAISE NOTICE 'entrou aqui 0';
-- Vou verificar que a dat_inicio agora inserida não é maior que a
-- última dat_inicio para o ID_QQ_COISA.
-- Vou ver qual a dat_inicio máxima para o ID_QQ_COISA
EXECUTE 'SELECT MAX(dat_inicio) AS max_dat_inicio FROM '
|| quote_ident(pNOME_VIEW)
|| ' WHERE '
|| quote_ident(pCHAVE_1)
|| ' = '
|| quote_literal(pVAL_CHAVE_1)
INTO STRICT vMAX_DAT_INICIO_MAI_0;
-- Vou verificar que a nova data inserida agora não é inferior à
data máxima.
IF pVAL_CHAVE_2 vMAX_DAT_INICIO_MAI_0 THEN
RAISE NOTICE 'A data inicio tem de ser maior que o campo data
inicio anterior.';
RETURN; /* Sai da função */
END IF;
-- Vou inserir uma nova linha
-- SELECT dat_inicio FROM aae_hist_anuncios WHERE id_anuncio_externo
= '5';
-- select max(dat_inicio) as max_dat_inicio from atae_hist_anuncios
END IF;
END;
$BODY$
LANGUAGE PLpgSQL
RETURNS NULL ON NULL INPUT
VOLATILE
EXTERNAL SECURITY INVOKER;
[/code]
I'm doing something wrong?
Best Regards,
On Tue, May 4, 2010 at 11:23 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Andre Lopes lopes80an...@gmail.com writes:
If I use NULL in any parameter the function does not RAISE any NOTICE. I
can't pass NULL values in parameters?
Sure you can. Maybe you declared the function STRICT? That means to
not call it for a NULL.
regards, tom lane
[GENERAL] Dynamic SQL with pgsql, how to?
Hi, I need to write some dynamic SQL in pgsql. I have to do something like this: [code=SQL Server] SET @STRINGN = @STRINGN + ' AND A.' + @CAMPOFECINI + ' IN (SELECT MAX(B.' + @CAMPOFECINI + ') FROM ' + @TABLA + ' B WHERE B.ID_SOCIEDAD = A.ID_SOCIEDAD AND B.ID_EMPREGAD = A.ID_EMPREGAD' IF @F_ALTA IS NOT NULL SET @STRINGN = @STRINGN + ' AND B.DAT_INI_ACT_EMP = A.DAT_INI_ACT_EMP' SET @STRINGN = @STRINGN + ')' EXEC sp_executesql @STRINGN, N'@FINI2 datetime out, @FFIN2 datetime out, @CAMP2 varchar(50) out', @FINI out, @FFIN out , @CAMP out [/code] There is documentation on how can I do this in pgsql? Best Regards,
Re: [GENERAL] Dynamic SQL with pgsql, how to?
Thanks for the reply's, I need to do a Dynamic SELECT INTO. There is a way of doing it? Best Regards, On Mon, May 3, 2010 at 10:05 AM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Andre Lopes : Hi, I need to write some dynamic SQL in pgsql. There is documentation on how can I do this in pgsql? Sure, http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] User with GRANTS only on Views. Lock table on function will work?
Hi, I have a database were the users only can do operations using views, they have not access to physical tables. But I have a function with a lock on a physical table. Can I allow this users to run a function that locks a physical table? Best Regards,
[GENERAL] Problem saving emails to database.
Hi, It is the first time that I store emails in a database to send them later... Let me explain the problem... I'am sending text emails, and to break the lines of the message I use \n. The first problem was to stores the \. To INSERT \n I need to write \\n. When I do a SELECT I see \n but when I dump the database in the INSERTS I see \\n. The problem with this is that when I send an email the \n that I see in the SELECT returns simply n in the email and does not break the line. This problem have solution? Best Regards,
[GENERAL] How to allow PostgreSQL to accept remote connection?
Hi, I have a virtual machine with VMWARE, and I need to connect from outside the virtual machine to PostgreSQL. How can I configure the PostgreSQL to accept outside connections? Best Regards,
Re: [GENERAL] How to allow PostgreSQL to accept remote connection?
Thanks for the reply's, How can I configure pg_hba.conf to accept connections from all IP's What I have in this file is: #ipv4 host all all 127.0.0.1/32 md5 #ipv6 host all all ::1/128 md5 What I need to change? Best Regards, On Sat, Apr 24, 2010 at 4:15 PM, Fredric Fredricson fredric.fredric...@bonetmail.com wrote: Raymond O'Donnell wrote: On 24/04/2010 15:58, Andre Lopes wrote: Hi, I have a virtual machine with VMWARE, and I need to connect from outside the virtual machine to PostgreSQL. How can I configure the PostgreSQL to accept outside connections? It should be just the same as a real machine put the IP address of the VM's network interface in listen_addresses in postgresql.conf. You will probably also have to edit pg_hba.conf file (chaper 20.1 in the manual). /Fredric Ray.
[GENERAL] Lock table, best option?
Hi, I need to do a SELECT and an UPDATE, but I will have concurrent processes doing the same task. How can I prevent that the concurrent task don't have the same results in the SELECT? Locking a table? How can I do that? Best Regards,
[GENERAL] Problem compiling function with BEGIN WORK; COMMIT WORK;
Hi, I need to use the BEGIN WORK; and COMMIT WORK; to lock a table when I'am doing a SELECT and UPDATE operation. The code is not compiling, the error is: [error]ERROR: syntax error at or near work at character 1 QUERY: work CONTEXT: SQL statement in PL/PgSQL function apr_apanhar_ownership_email near line 7 [/error] And the code is: [code] CREATE OR REPLACE FUNCTION public.apr_apanhar_ownership_email (ppid int4, out ppid_email_envio int4) RETURNS integer AS $body$ DECLARE pPID alias for $1; vID_EMAIL_ENVIOint4; BEGIN begin work; lock table atem_emails_envios in access exclusive mode; select id_email_envio from atem_emails_envios where dat_sended is null and (i_started is null or i_started (current_timestamp - '2 hours'::interval)) and (pid is null or pid = pPID) order by dat_inserted asc limit 1 into vID_EMAIL_ENVIO; update atem_emails_envios set i_started = current_timestamp, pid = pPID where id_email_envio = vID_EMAIL_ENVIO; commit work; ppid_email_envio := vID_EMAIL_ENVIO; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER; [/code] What is wrong here? Can someone give me a clue. Best Regards,
[GENERAL] Database viewpoint of subject - Sending e-mails from database table with Cronjob
Hi, I need some advice about a subject. I generate e-mail messages to a database table and then with a CronJob I sent the e-mails. My doubt is... The CronJob runs every 10 minutes, but If I have 100.000 e-mails to send the script will not be able to send all the 100.000 e-mails in 10 minutes. How can I deal with this problem? There is no problem to have multiple CronJobs runing in background? Please give me some clues about this subject. Best Regards,
Re: [GENERAL] Database viewpoint of subject - Sending e-mails from database table with Cronjob
Hi, Thanks for the reply. [quote] The other way is to let the cron job spawn new processes (up to a limited number of child proceses) as long as there are mails to send. These child processes runs as long as there are mails to send, then they die. The cron job will then mostly do process controll/start new processes. [/quote] How can I do this. I'am thinking to use a PHP Script? Best Regards, On Wed, Apr 21, 2010 at 12:35 PM, A B gentosa...@gmail.com wrote: I generate e-mail messages to a database table and then with a CronJob I sent the e-mails. My doubt is... The CronJob runs every 10 minutes, but If I have 100.000 e-mails to send the script will not be able to send all the 100.000 e-mails in 10 minutes. How can I deal with this problem? There is no problem to have multiple CronJobs runing in background? If N is the number of mails you can send per cronjob in 10 minutes, then run 100 000/N cron jobs. You will be very happy if you also add som kind of sleep (for a growing number of seconds) to the cronjobs when there are currently no more mails to send. Otherwise you will end up with a very annoying CPU load. The other way is to let the cron job spawn new processes (up to a limited number of child proceses) as long as there are mails to send. These child processes runs as long as there are mails to send, then they die. The cron job will then mostly do process controll/start new processes. That ought to do the trick.
Re: [GENERAL] Database viewpoint of subject - Sending e-mails from database table with Cronjob
It is not for SPAM, the 100.000 e-mails is only for example purposes. But I must to draw the code to be possible to send this volume, just in case if it happens. There are examples on how can I do this? Best Regards, On Wed, Apr 21, 2010 at 4:01 PM, Allan Kamau kamaual...@gmail.com wrote: On Wed, Apr 21, 2010 at 3:41 PM, Andre Lopes lopes80an...@gmail.com wrote: Hi, Thanks for the reply. [quote] The other way is to let the cron job spawn new processes (up to a limited number of child proceses) as long as there are mails to send. These child processes runs as long as there are mails to send, then they die. The cron job will then mostly do process controll/start new processes. [/quote] How can I do this. I'am thinking to use a PHP Script? Best Regards, On Wed, Apr 21, 2010 at 12:35 PM, A B gentosa...@gmail.com wrote: I generate e-mail messages to a database table and then with a CronJob I sent the e-mails. My doubt is... The CronJob runs every 10 minutes, but If I have 100.000 e-mails to send the script will not be able to send all the 100.000 e-mails in 10 minutes. How can I deal with this problem? There is no problem to have multiple CronJobs runing in background? If N is the number of mails you can send per cronjob in 10 minutes, then run 100 000/N cron jobs. You will be very happy if you also add som kind of sleep (for a growing number of seconds) to the cronjobs when there are currently no more mails to send. Otherwise you will end up with a very annoying CPU load. The other way is to let the cron job spawn new processes (up to a limited number of child proceses) as long as there are mails to send. These child processes runs as long as there are mails to send, then they die. The cron job will then mostly do process controll/start new processes. That ought to do the trick. I really hope this is not for SPAM mail. Allan.
Re: [GENERAL] Database viewpoint of subject - Sending e-mails from database table with Cronjob
Thanks for the reply.
I will move the topic to the php/pgsql if I don't get how to do it.
Thanks for the help.
Best Regards,
On Wed, Apr 21, 2010 at 4:49 PM, Bill Moran wmo...@potentialtech.comwrote:
In response to A B gentosa...@gmail.com:
[quote]
The other way is to let the cron job spawn new processes (up to a
limited number of child proceses) as long as there are mails to send.
These child processes runs as long as there are mails to send, then
they die. The cron job will then mostly do process controll/start new
processes.
[/quote]
How can I do this. I'am thinking to use a PHP Script?
This is getting a little of topic, but I guess something like
exec('php some_script.php') will work?
There's a php/pgsql list available on this listserve that will get
you a lot more help in this area.
However, I would suggest PHP's pcntl_fork() and friends for creating
multiple processes. Of course, that's only needed if you want to
run them in parallel, which was unclear to me.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/http://people.collaborativefusion.com/%7Ewmoran/
[GENERAL] How to call a file from a PlPgsql trigger?
Hi, I have an account in a shared hosting account and I can't install any 3rd party software. I need to send data from a PostgreSQL database to a MySQL database. I was thinking of using a trigger. The trigger calling a PHP file to help me passing the data to the MySQL database. It is possible to call a file from a PlPgsql trigger? If yes, how to? This is the best way to do this task? PS: Sorry for my english. Best Regards, André
Re: [GENERAL] How to call a file from a PlPgsql trigger?
Hi, Thanks for the reply. I can't install languages. The database is in a shared hosting. There are other solutions? Best Regards, On Mon, Apr 19, 2010 at 10:00 AM, Pavel Stehule pavel.steh...@gmail.comwrote: Hello 2010/4/19 Andre Lopes lopes80an...@gmail.com: Hi, I have an account in a shared hosting account and I can't install any 3rd party software. I need to send data from a PostgreSQL database to a MySQL database. I was thinking of using a trigger. The trigger calling a PHP file to help me passing the data to the MySQL database. use plperlu instead. You can put connect and transfer of data directly to trigger regards Pavel Stehule http://www.postgres.cz/index.php/PL/Perlu_-_Untrusted_Perl_%28en%29 It is possible to call a file from a PlPgsql trigger? If yes, how to? This is the best way to do this task? PS: Sorry for my english. Best Regards, André
Re: [GENERAL] How to call a file from a PlPgsql trigger?
Ok, So the best option is to send the data to a table and then with an cronjob I verify if it is data to send to the MySQL database. Best Regards, André. On Mon, Apr 19, 2010 at 10:09 AM, Pavel Stehule pavel.steh...@gmail.comwrote: 2010/4/19 Andre Lopes lopes80an...@gmail.com: Hi, Thanks for the reply. I can't install languages. The database is in a shared hosting. There are other solutions? yes. you can store values to some stack table (via trigger) and later move content of this table to mysql from application. But you can't execute file from stored procedure as unprivileged user. Pavel Best Regards, On Mon, Apr 19, 2010 at 10:00 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello 2010/4/19 Andre Lopes lopes80an...@gmail.com: Hi, I have an account in a shared hosting account and I can't install any 3rd party software. I need to send data from a PostgreSQL database to a MySQL database. I was thinking of using a trigger. The trigger calling a PHP file to help me passing the data to the MySQL database. use plperlu instead. You can put connect and transfer of data directly to trigger regards Pavel Stehule http://www.postgres.cz/index.php/PL/Perlu_-_Untrusted_Perl_%28en%29 It is possible to call a file from a PlPgsql trigger? If yes, how to? This is the best way to do this task? PS: Sorry for my english. Best Regards, André
[GENERAL] Error in Trigger function. How to correct?
Hi, I have a trigger that runs in my Development machine but not in my Production machine. the code is the following: [code] CREATE OR REPLACE FUNCTION aprtr_geraemail_agcompagamento () RETURNS trigger AS $BODY$ DECLARE vSUBJECT varchar(500); vEMAIL_MSG_BRUTO text; vEMAIL_MSG_COMPOSTA text; -- Tem o body do email já preenchido. vEMAIL_TO varchar(500); -- Variaveis de configuração vID_EMAIL_MSG varchar(20); vEMAIL_FROM varchar(500); vMAX_TRIES int4; BEGIN -- ## CONFIGURACOES vID_EMAIL_MSG := 'ag_com_pag_sucesso'; -- campo id_email_msg vEMAIL_FROM := 'adefi...@mail.com'; vMAX_TRIES := 3; -- Número máximo de vezes que a mensagem vai tentar ser enviada -- ## -- CONDICOES PARA REALIZAR AS TAREFAS if new.id_estado_insercao = 'sucesso' and new.id_estado_concordancia_contrato = 'aceite' and new.id_estado_concordancia_pagamento = 'aceite' and new.id_estado_pagamento = 'pago' then -- Vou buscar a mensagem de email (ag_com_pag_sucesso) select email_subject, email_msg from aem_hist_mensagens_email where id_email_msg = vID_EMAIL_MSG and dat_fim is null into vSUBJECT, vEMAIL_MSG_BRUTO; -- Vou fazer a mensagem de email select replace(replace(replace(replace(replace(replace(vEMAIL_MSG_BRUTO, '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio), '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@', a.telefone_anuncio), '@orientacao_sexual@', o.n_orientacao), '@cidade@', c.n_cidade) as email_composto, a.email as email_to -- a.id_anuncio_externo, a.n_anuncio, a.telefone_anuncio, a.dat_nasc, -- c.n_cidade, n.n_nacionalidade, o.n_orientacao from aae_anuncios a join aa_cidades c ON a.id_cidade = c.id_cidade join ae_nacionalidades n ON a.id_nacionalidade = n.id_nacionalidade join ae_orientacao o ON a.id_orientacao = o.id_orientacao where id_anuncio_externo = OLD.id_anuncio_externo into vEMAIL_MSG_COMPOSTA, vEMAIL_TO; -- Vou inserir na tabela de mensagens de email (atem_emails_envios) insert into aem_emails_envios (id_email_msg, dat_inserted, max_tries, email_from, email_to, email_subject, email_msg) values (vID_EMAIL_MSG, now(), vMAX_TRIES, vEMAIL_FROM, vEMAIL_TO, vSUBJECT, vEMAIL_MSG_COMPOSTA); -- DEBUG -- raise notice ' % ', vEMAIL_MSG_COMPOSTA; end if; RETURN NULL; END; $BODY$ LANGUAGE PLpgSQL CALLED ON NULL INPUT VOLATILE EXTERNAL SECURITY DEFINER; [/code] This code works great in my development machine in Windows. When I move this to the production machine gives me this error: [error] SQL Error: ERROR: function replace(text, unknown, integer) does not exist LINE 1: select replace(replace(replace(replace(replace(replace( $1 ,... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: select replace(replace(replace(replace(replace(replace( $1 , '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio), '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@', a.telefone_anuncio), '@orientacao_sexual@', o.n_orientacao), '@cidade@', c.n_cidade) as email_composto, a.email as email_to from aae_anuncios a join aa_cidades c ON a.id_cidade = c.id_cidade join ae_nacionalidades n ON a.id_nacionalidade = n.id_nacionalidade join ae_orientacao o ON a.id_orientacao = o.id_orientacao where id_anuncio_externo = $2 CONTEXT: PL/pgSQL function aprtr_geraemail_agcompagamento line 46 at SQL statement [/error] What can I do to correct this? Some clues? Best Regards.
Re: [GENERAL] [SOLVED] Error in Trigger function. How to correct?
Thanks a lot, it works! I'am using Postgres Plus Advanced Server 8.3R2 in development.In production I user PostreSQL 8.3.9. Best Regards, On Wed, Apr 14, 2010 at 2:19 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Andre Lopes : Hi, I have a trigger that runs in my Development machine but not in my Production machine. the code is the following: SQL Error: ERROR: function replace(text, unknown, integer) does not exist LINE 1: select replace(replace(replace(replace(replace(replace( $1 ,... ^ Let me guess: you have 8.2 on your Development machine and 8.3 or 8.4 on your production machine, right? I think you need to add explicit casts, let me explain: HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: select replace(replace(replace(replace(replace(replace( $1 , '@numero_anuncio@', a.id_anuncio_externo), '@nome_anuncio@', a.n_anuncio), '@idade@', EXTRACT(year from AGE(NOW(), a.dat_nasc))), '@telefone_anuncio@', EXTRACT(year ...) returns an INT, but replace(...) expects a TEXT or a similar type. Try to add a ::TEXT after the EXTRACT(...) - function: extract (year from ...)::text Maybe there are more occurrences ... Greetings from saxony, germany. -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to generate a valid postgre TIMESTAMP with PHP?
Hi, I'am writing some code in PHP and I need to generate a valid postgresql TIMESTAMP with PHP. Any PHP programmer thar can help me on how to generate valid TIMESTAMP's with PHP? Sorry my bad english. Best Regards,
[GENERAL] How to write Rules on a View to allow all actions as in the physical table?
Hi, I have one problem with a view and his rules. Ok, I have a table to store Session data, the structure is this: [code] CREATE TABLE cti_sessions ( session_id varchar(40) NOT NULL DEFAULT 0, ip_address varchar(16) NOT NULL DEFAULT 0, user_agent varchar(50) NOT NULL, last_activity int4 NOT NULL DEFAULT 0, user_data text, coment varchar(2000), id_utiliz_ins varchar(45), id_utiliz_upd varchar(45), data_ult_actual timestamp, PRIMARY KEY(session_id), CONSTRAINT ckeck_last_activity CHECK(last_activity = 0) ); [/code] And I have a view with this structure: [code] CREATE OR REPLACE VIEW ci_sessions AS select session_id, ip_address, user_agent, last_activity, user_data from cti_sessions; CREATE OR REPLACE RULE ins_ci_sessions AS ON INSERT TO ci_sessions DO INSTEAD (insert into cti_sessions (session_id, ip_address, user_agent, last_activity, user_data) values (new.session_id, new.ip_address, new.user_agent, new.last_activity, new.user_data)); CREATE OR REPLACE RULE del_ci_sessions AS ON DELETE TO ci_sessions DO INSTEAD (delete from cti_sessions where session_id = old.session_id); CREATE OR REPLACE RULE upd_ci_sessions AS ON UPDATE TO ci_sessions DO INSTEAD (update cti_sessions set ip_address = new.ip_address, user_agent = new.user_agent, last_activity = new.last_activity, user_data = new.user_data where session_id = old.session_id); [/code] If I use the physical table do to the operations with sessions it works OK. If I use the view it won't works. How can I write the Rules to allow do all as in the physical table? Best Regards,
[GENERAL] What datatype should I use to store an IP Address?
Hi, I'm using Varchar(16) to store an IP Address. PostgreSQL offers another datatypes to store IP's or it is OK to store in Varchar(16)? Best Regards,
Re: [GENERAL] How to? Timestamp with timezone.
Hi, Thanks for the reply's. [code] ALTER DATABASE foo SET timezone TO 'someval' ALTER ROLE bar SET timezone TO 'someval' [/code] I need to alter only the Timezone of the database OR I need also to alter the Role? Best Regards, On Wed, Mar 10, 2010 at 1:06 AM, Alvaro Herrera alvhe...@commandprompt.comwrote: Andre Lopes escribió: It is possible to do this in a shared database server? You can also do ALTER DATABASE foo SET timezone TO 'someval' ALTER ROLE bar SET timezone TO 'someval' -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
[GENERAL] How to? Timestamp with timezone.
Hi, I have a database in a US based Server. I need to get a TIMESTAMP with the PORTUGAL time. How can I do this? Best Regards,
