Re: [GENERAL] Storing HTML: HTML entities being rendered in that raw form
linnewbie wrote: What I have on disk would be: p Bonnie amp; Clyde/p which would usually be rendered as: Bonnie Clype but this is not happening, it's being rendered as: Bonnie amp; Clyde There are only three options. 1. That is *not* what you have stored in the database, it's being escaped on the way in. 2. You aren't displaying what *is* stored in the database, it's being escaped on the way out. 3. You are rendering it as text, not HTML Break your problem into pieces and test each in turn. Store exactly that string and check it with psql. Examine exactly what you have when you read it from the database. Display two copies - one you have from the database and one you manually set in a variable. Do they differ? -- Richard Huxton Archonet Ltd -- 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] Storing HTML: HTML entities being rendered in that raw form
linnewbie wrote: On Apr 9, 1:00 pm, st...@blighty.com (Steve Atkins) wrote: On Apr 9, 2009, at 9:27 AM, linnewbie wrote: Hi all, I have stored HTML in a text field that I subsequently render on the web. However when I retrieve and render this data on the web I am getting the entities being rendered in their raw form, ie, instead of getting the '' symbol when 'amp;' is stored I'm getting the 'raw' 'amp;'. I would be grateful if anyone can point out how I can get around this. It's a problem in your code, not the database. You're probably escaping it one time to many or unescaping it one time too few. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general To clarify, I am not escaping the string in any way. Say the page I am saving the database is the about us page form a company website. First, make a from to create the about us page in a text area field, then I copy the html from my text editor and past it ino this text area from. I then have a cgi script which takes the contents from the text area field and stores it in the database. What I have on disk would be: .. p Bonnie amp; Clyde/p which would usually be rendered as: Bonnie Clype but this is not happening, it's being rendered as: Bonnie amp; Clyde That's because, as someone else suggested, something is quoting the . In order to be rendered 'Bonnie amp; Clyde' by the browser, it needs to be 'Bonnie amp;amp; Clyde' in the HTML (just view the HTML source from inside the browser). You haven't provided any detail on the rendering phase, BTW. .TM. -- 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] Evidently no support for the mmddyyyy date format
On 09/04/2009 23:56, Bernard Barton wrote: Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried How exactly are you sending these values to the database? Straight SQL, or some other mechanism? Can you show us some examples? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Evidently no support for the mmddyyyy date format
Hello use to_date function, please. postgres=# select to_date('10122008','DDMM'); to_date 2008-12-10 (1 row) Time: 1,152 ms postgres=# regards Pavel Stehule 2009/4/10 Raymond O'Donnell r...@iol.ie: On 09/04/2009 23:56, Bernard Barton wrote: Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried How exactly are you sending these values to the database? Straight SQL, or some other mechanism? Can you show us some examples? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] index usage in various scenarious
Hello, QUESTION1: Can somebody clarify in what of the proposed scenarios is the following index used? Any further comment will be greatly appreciated. QUESTION2: Does any other scenarios when an index is NOT used (and someone might possibly expect it is used) come to your mind? Thank you very much for your time and wish you a happy day:-) CREATE INDEX idx_margincall_bussdt ON margincall USING btree (businessdate); SCENARIO 1: select MC.IDTRADINGACCOUNT from MARGINCALL MC join DEFAULTINGACCOUNT DA on DA.BUSINESSDATE = MC.BUSINESSDATE and DA.IDTRADINGACCOUNT = MC.IDTRADINGACCOUNT SCENARIO 2: select MC.IDTRADINGACCOUNT from MARGINCALL MC where MC.BUSINESSDATE = '17.11.2008'; SCENARIO 3: select MC.IDTRADINGACCOUNT from MARGINCALL MC join DEFAULTINGACCOUNT DA on DA.BUSINESSDATE = MC.BUSINESSDATE and DA.IDTRADINGACCOUNT = MC.IDTRADINGACCOUNT where MC.BUSINESSDATE = '17.11.2008'; SCENARIO 4 + QUESTION: Is usage of indexes during execution of queries affected by db-links? E.g.: select MC.IDTRADINGACCOUNT from marginc...@db_link_name MC where MC.BUSINESSDATE = '17.11.2008'; Adam Slachta
Re: [GENERAL] Evidently no support for the mmddyyyy date format
This is embedded SQL in a .pgc file. You can see the c_docket_date between :date1 and :date2 line in the select statement, which is where the dates are porcessed. If I pass a date in the mm-dd- format it works. However, the application I'm porting is all based on dates in the mmdd format. I'm 99% cerain that PostgreSQL will NOT support dates in the mmdd format, unless you use the to_date function, which I'm trying to avoid. select c_jnum_prefix, c_jnum_seq, c_jnum_year, c_jnum_suffix, c_jnum_venue ,c_actkey, c_disp_cd into :prfx, :seq, :yr, :sfx, :ven, :actkey, :disp from c_records where c_jnum_prefix = :prfx and c_jnum_seq between :seq1 and :seq2 and c_jnum_venue = :ven and c_docket_date between :date1 and :date2 order by c_jnum_prefix,c_jnum_seq,c_jnum_year, c_jnum_suffix,c_jnum_venue; - Original Message - From: Raymond O'Donnell r...@iol.ie To: Bernard Barton bf...@comcast.net Cc: pgsql-general@postgresql.org Sent: Friday, April 10, 2009 5:31:45 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] Evidently no support for the mmdd date format On 09/04/2009 23:56, Bernard Barton wrote: Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried How exactly are you sending these values to the database? Straight SQL, or some other mechanism? Can you show us some examples? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
Re: [GENERAL] Evidently no support for the mmddyyyy date format
Yes, I mentioned that I could use the to_date function, but as I said, that would involve a LOT of changes to a LOT of source code, which I'm trying to avoid. -Thanks - Original Message - From: Pavel Stehule pavel.steh...@gmail.com To: r...@iol.ie Cc: Bernard Barton bf...@comcast.net, pgsql-general@postgresql.org Sent: Friday, April 10, 2009 5:40:21 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] Evidently no support for the mmdd date format Hello use to_date function, please. postgres=# select to_date('10122008','DDMM'); to_date 2008-12-10 (1 row) Time: 1,152 ms postgres=# regards Pavel Stehule 2009/4/10 Raymond O'Donnell r...@iol.ie: On 09/04/2009 23:56, Bernard Barton wrote: Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried How exactly are you sending these values to the database? Straight SQL, or some other mechanism? Can you show us some examples? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Evidently no support for the mmddyyyy date format
2009/4/10 bf...@comcast.net: Yes, I mentioned that I could use the to_date function, but as I said, that would involve a LOT of changes to a LOT of source code, which I'm trying to avoid. other solution is custom datatype. It isn't too much work, but it is coding in C. regards Pavel Stehule -Thanks - Original Message - From: Pavel Stehule pavel.steh...@gmail.com To: r...@iol.ie Cc: Bernard Barton bf...@comcast.net, pgsql-general@postgresql.org Sent: Friday, April 10, 2009 5:40:21 AM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] Evidently no support for the mmdd date format Hello use to_date function, please. postgres=# select to_date('10122008','DDMM'); to_date 2008-12-10 (1 row) Time: 1,152 ms postgres=# regards Pavel Stehule 2009/4/10 Raymond O'Donnell r...@iol.ie: On 09/04/2009 23:56, Bernard Barton wrote: Today I tried every permutation of the DateStyle parameter I could find, and still cannot get PostgreSQL 8.3 to accept dates in the format mmdd. I tried How exactly are you sending these values to the database? Straight SQL, or some other mechanism? Can you show us some examples? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] complicated query (newbie..)
Thanks a lot, Sam (and others), thanks to your help I managed to finally produce the query I wanted. Regards, mk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Querying a Large Partitioned DB
Team Amazing, I am building a massive database for storing the syslogs of a room of servers. The database gets about 25 million entries a day, and need to keep them for 180 days. So the total size of the database will be about 4.5 billion records. I need to be able to do full text searches on the message field, and of course, it needs to be reasonably fast. The table is partitioned daily and has this structure: syslog=# \d List of relations Schema |Name | Type | Owner +-+---+--- public | systemevents| table | pgsql public | systemevents_032909 | table | pgsql public | systemevents_033009 | table | pgsql public | systemevents_033109 | table | pgsql public | systemevents_040109 | table | pgsql public | systemevents_040209 | table | pgsql public | systemevents_040309 | table | pgsql public | systemevents_040409 | table | pgsql public | systemevents_040509 | table | pgsql public | systemevents_040609 | table | pgsql public | systemevents_040709 | table | pgsql public | systemevents_040909 | table | pgsql public | systemevents_041009 | table | pgsql (13 rows) syslog=# \d systemevents Table public.systemevents Column |Type | Modifiers +-+--- message| character varying | facility | integer | fromhost | character varying(80) | priority | integer | devicereportedtime | timestamp without time zone | receivedat | timestamp without time zone | infounitid | integer | syslogtag | character varying(80) | message_index_col | tsvector| Rules: systemevents_insert_032909 AS ON INSERT TO systemevents WHERE new.devicereportedtime '2009-03-28 23:59:59'::timestamp without time zone AND new.devicereportedtime = '2009-03-29 23:59:59'::timestamp without time zone DO INSTEAD INSERT INTO systemevents_032909 (message, facility, fromhost, priority, devicereportedtime, receivedat, infounitid, syslogtag, message_index_col) .. [there are rules like that for each partition] My typical query looks like this: SELECT * FROM SystemEvents WHERE message_index_col @@ to_tsquery('english', 'Term') LIMIT 25 OFFSET 0; Here is an explain analyze: Limit (cost=2422393.00..2422393.06 rows=25 width=153) (actual time=93363.496..93363.610 rows=25 loops=1) - Sort (cost=2422393.00..2422933.05 rows=216019 width=153) (actual time=93363.490..93363.532 rows=25 loops=1) Sort Key: public.systemevents.devicereportedtime Sort Method: top-N heapsort Memory: 22kB - Result (cost=0.00..2416297.10 rows=216019 width=153) (actual time=20567.267..93362.574 rows=163 loops=1) - Append (cost=0.00..2415217.01 rows=216019 width=153) (actual time=20567.244..93361.582 rows=163 loops=1) - Seq Scan on systemevents (cost=0.00..1750240.39 rows=30891 width=153) (actual time=20567.238..91580.249 rows=24 loops=1) Filter: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040309 systemevents (cost=1168.86..54860.45 rows=15253 width=152) (actual time=82.429..275.589 rows=20 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040309 (cost=0.00..1165.04 rows=15253 width=0) (actual time=50.029..50.029 rows=20 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040409 systemevents (cost=1038.56..52300.49 rows=14601 width=147) (actual time=68.006..68.006 rows=0 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040409 (cost=0.00..1034.91 rows=14601 width=0) (actual time=67.999..67.999 rows=0 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040509 systemevents (cost=1055.06..52482.72 rows=14644 width=150) (actual time=63.257..63.257 rows=0 loops=1) Recheck Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Index Scan on systemevents_msg_idx_040509 (cost=0.00..1051.40 rows=14644 width=0) (actual time=63.251..63.251 rows=0 loops=1) Index Cond: (message_index_col @@ '''funkju'''::tsquery) - Bitmap Heap Scan on systemevents_040609 systemevents (cost=1842.50..88135.00 rows=24506 width=152) (actual time=117.747..355.043 rows=34 loops=1) Recheck Cond:
[GENERAL] Multiple character encodings within a single database/table?
If I have the C locale, can I have multiple character encodings within: 1. A single database? 2. A single table? More specifically, I would like to be able to have Unicode columns and ASCII text columns within the same table. Is this possible? If so, how do I achieve it? It was not clear to me from: http://www.postgresql.org/docs/current/static/multibyte.html It seems to me from this statement: It can be overridden when you create a database, so you can have multiple databases each with a different character set. That it may be database wide, but I am not sure that it is not possible to have both ordinary char and Unicode in the same table. Possible or not? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT or UPDATE
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of James B. Byrne Sent: Monday, April 06, 2009 1:46 PM To: pgsql-general@postgresql.org Subject: [GENERAL] INSERT or UPDATE I have spent the last couple of days reading up on SQL, of which I know very little, and PL/pgSQl, of which I know less. I am trying to decide how best to approach the following requirement. Given a legal name and a common name and associated details, we wish to insert this information into a table, entities. As well, we believe it useful to allow a specific entity more than one common name. So, at the moment we are considering having another table, identifiers, that takes entity_id = entity.id (synthetic sequenced PK for entities), the identifier_type (always 'AKNA' for this collection of identifiers) and identifier_value = entity.common_name. This seems straight forward enough when initially inserting an entity. However, it is conceivable that over the lifetime of the system a particular entity might change its common name. For example the former John Tash Enterprises might become popularly known as JTE Inc. while the legal name remains unchanged. When we update the entity record and set the common_name = JTE Inc. then we need insert an identifier row to match. However, identifiers for a given entity can be maintained separately from the entity itself. It is therefore possible, indeed likely, that the identifier JTE Inc. for that entity already exists. Likely, but not certain. In any case, the old identifier row remains unchanged after the new is inserted. The issue then is how to determine on an UPDATE entities whether it is necessary to INSERT a new identifier using values provided from the entities row. From what I have gathered, what one does is simply insert the new identifiers row. If there is a primary key conflict then the update fails, which the function handles gracefully. If not, then it succeeds. I have also formed the opinion that what one does is write a function or functions, such as fn_aknau(entity_id, name), and tie these with triggers to the appropriate actions on entities such as: CREATE TRIGGER tr_entities_aioru AFTER INSERT OR UPDATE ON entities FOR EACH ROW EXECUTE fn_aknai(entities.id, 'AKNA', entities.common_name); Is my appreciation correct or am I missing the mark entirely? Is this considered the proper place and means to accomplish this sort of task in an RDBMS? Does it belong elsewhere? Am I correct in inferring that the values in the columns id and common_name will be those of entities AFTER the insert or update and that these will be available to the body of the function? Is the trigger dependent upon a SUCCESSFUL INSERT or UPDATE of entities or will it be called regardless? Must the function be written in PL/pgSQl (or similar PL) or could this function be written in straight SQL? Should it be straight SQL if possible? What should the function return, if anything? Fairly basic stuff I am sure but somewhat mystifying for me at the moment. Any help would be appreciated. It is a difficult question. For instance, there are many possibilities when a collision occurs. I guess that for some collisions, sharing the name is OK. Consider two different fictional companies (hopefully in different domains): Jet Propulsion Industries Incorporated == JPI Inc. (makes jet engines) Journey Protection Investments Inc. == JPI Inc. (underwrites travel insurance) Probably, they don't have a legal battle because they have completely different domains. So it seems OK for both companies to relate to this entity if it is only used as a label. On the other hand, you may have a typographical error on data entry for a computer firm. If you label a company as IBM when it should have been IBN I guess that won't make anyone happy. I think that the real issue is that you must truly and carefully identify your business rules and model those in the database structure. Said another way, How would a human handle this issue given a name collision? If the answer is not obvious, then maybe you need to write an exceptions log and handle each case by hand that is not solved by a simple and clear to understand rule. -- 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] Querying a Large Partitioned DB
Justin Funk fun...@iastate.edu writes: Can you give me any tips and suggestions about how to speed this up? Use fewer partitions --- 180 is a lot. Maybe weekly partitioning would be about right. regards, tom lane -- 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] Evidently no support for the mmddyyyy date format
Pavel Stehule wrote: 2009/4/10 bf...@comcast.net: Yes, I mentioned that I could use the to_date function, but as I said, that would involve a LOT of changes to a LOT of source code, which I'm trying to avoid. other solution is custom datatype. It isn't too much work, but it is coding in C. Yep, that was my first idea too. You would copy an existing data type, and modify just the _input_ routine to handle input with no delimiters. I would simply add the delimiters and pass the string to the original input function; it really isn't that much work. The only downside is that you have to create/use a custom data type for this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Multiple character encodings within a single database/table?
Dann Corbit dcor...@connx.com writes: If I have the C locale, can I have multiple character encodings within: 1. A single database? 2. A single table? No. regards, tom lane -- 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] Querying a Large Partitioned DB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Apr 10, 2009, at 10:15 AM, Justin Funk wrote: Team Amazing, I am building a massive database for storing the syslogs of a room of servers. The database gets about 25 million entries a day, and need to keep them for 180 days. So the total size of the database will be about 4.5 billion records. I need to be able to do full text searches on the message field, and of course, it needs to be reasonably fast. You could use pg-pool II or your own middleware to execute the search query in parallel across all the partitions (maybe not all 180 at once, though). Cheers, M -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.10 (Darwin) iEYEARECAAYFAknfaVAACgkQqVAj6JpR7t65DQCgsN51pMWoY8WXyxss6cXRPHug 4h8An2IufbKuhrw4fyki4gBbjrkkQD0M =5PRb -END PGP SIGNATURE- -- 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] Evidently no support for the mmddyyyy date format
- bf...@comcast.net wrote: This is embedded SQL in a .pgc file. You can see the c_docket_date between :date1 and :date2 line in the select statement, which is where the dates are porcessed. If I pass a date in the mm-dd- format it works. However, the application I'm porting is all based on dates in the mmdd format. I'm 99% cerain that PostgreSQL will NOT support dates in the mmdd format, unless you use the to_date function, which I'm trying to avoid. select c_jnum_prefix, c_jnum_seq, c_jnum_year, c_jnum_suffix, c_jnum_venue ,c_actkey, c_disp_cd into :prfx, :seq, :yr, :sfx, :ven, :actkey, :disp from c_records where c_jnum_prefix = :prfx and c_jnum_seq between :seq1 and :seq2 and c_jnum_venue = :ven and c_docket_date between :date1 and :date2 order by c_jnum_prefix,c_jnum_seq,c_jnum_year, c_jnum_suffix,c_jnum_venue; I might be missing something, but could you not preprocess the :date1 and :date2 variables before passing them to the SQL code above. Adrian Klaver akla...@comcast.net -- 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] Multiple character encodings within a single database/table?
On Mar 23, 2009, at 3:50 PM, Dann Corbit wrote: If I have the C locale, can I have multiple character encodings within: 1. A single database? 2. A single table? No. More specifically, I would like to be able to have Unicode columns and ASCII text columns within the same table. Is this possible? If so, how do I achieve it? Any valid ascii string is also a valid utf8 string. You can probably just use utf8 for everything. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ34pd2FH5GXCfxAsRAlbLAJ95Yk0Ab5Zak5B7sRl6ux2ybgrR5ACgl+ZH wB8tgw7sotlCE/bCakW+OC4= =ATNN -END PGP SIGNATURE- -- 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] Multiple character encodings within a single database/table?
Steve Atkins wrote: On Mar 23, 2009, at 3:50 PM, Dann Corbit wrote: If I have the C locale, can I have multiple character encodings within: 1. A single database? 2. A single table? No. More specifically, I would like to be able to have Unicode columns and ASCII text columns within the same table. Is this possible? If so, how do I achieve it? Any valid ascii string is also a valid utf8 string. You can probably just use utf8 for everything. More specifically, ASCII bytes are valid UTF8 values. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Internationalization
Pedro Doria Meunier wrote: Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier How about parent child table layout. The child table has one record for translation for each document. something like this Create table ParentDoc ( docid serial, description text ) Create table ChildDoc ( docid integer, doc_text text, short_description text, language text ) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: array subscript out of range
Hello all, Need ur help.I dont know wats wrong with the following fucntion. create or replace function twoarray() returns setof integer as ' declare i integer; j integer; a integer[][]; begin for i in 1..10 loop for j in 1..2 loop a[i][j]:=i*j; return next a[i][j]; end loop; end loop; return; end; ' language 'plpgsql'; when i execute the following statement i get an error saying 'array subscript out of range' select * from twoarray(); ERROR: array subscript out of range CONTEXT: PL/pgSQL function twoarray line 8 at assignment Help me,please -- View this message in context: http://www.nabble.com/ERROR%3A-array-subscript-out-of-range-tp22992481p22992481.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: array subscript out of range
chinchu2005 chinchu2...@gmail.com writes: declare i integer; j integer; a integer[][]; begin for i in 1..10 loop for j in 1..2 loop a[i][j]:=i*j; This isn't going to work --- it implies dynamically resizing the array, and plpgsql isn't smart enough to do that for a multidimensional array. Do you actually need a 2-D array here? If so you'll have to initialize it to the right dimensions to start with, eg with a := '{{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}}'; regards, tom lane -- 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] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Justin, First of all thank you for your input. :) Actually what I have is a fully internationalized site by means of getttext. *Some* of the content comes from the PGSQL database where 2 tables relation with others (namely for sensor data description). These tables have the simplest arrangement: id, description :] I wondered if there was some sort of pgsql extension providing a text replacement mechanism of sorts in order to achieve something like gettext ... I guess I'll have to resort to what I've previously thought of ... Regards, Pedro Doria Meunier. Justin wrote: Pedro Doria Meunier wrote: Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier How about parent child table layout. The child table has one record for translation for each document. something like this Create table ParentDoc ( docid serial, description text ) Create table ChildDoc ( docid integer, doc_text text, short_description text, language text ) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ3/bA2FH5GXCfxAsRAlZ0AKCnP9LDOJlOrDSF+Ci4G3CpdX8AlgCdFJqQ 5CrOIuWvgVBXCmyZwhjR8r8= =DmmH -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: array subscript out of range
chinchu2005 escribió: Hello all, Need ur help.I dont know wats wrong with the following fucntion. create or replace function twoarray() returns setof integer as ' declare i integer; j integer; a integer[][]; begin for i in 1..10 loop for j in 1..2 loop a[i][j]:=i*j; We just had this question on the spanish list. Somebody was kind enough to write the answers down in the Wiki: http://wiki.postgresql.org/wiki/Matrices_Multidimensionales_con_funciones It is all in spanish, but the code examples should be helpful nonetheless. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general