[SQL] Trailing spaces - is there an easier way?
I'm sure this has been asked before, but I couldn't find it: I have a "zzz CHAR (8)" field. It needs to be CHAR because trailing spaces need to be ignored for most operations. However, I need to concatenate it with another (literal) string and need the spaces to be significant in that operation. The *ONLY WAY* I could find to do it in v9.0.1 was (represented in the following function): CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT RETURNS NULL ON NULL INPUT IMMUTABLE LANGUAGE SQL AS $SQL$ SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) $SQL$; And then of course I write: SELECT padded( zzz ) || '/' || ... Is there a better way?
Re: [SQL] Trailing spaces - is there an easier way?
2010/11/4 Dean Gibson (DB Administrator) : > I'm sure this has been asked before, but I couldn't find it: > > I have a "zzz CHAR (8)" field. It needs to be CHAR because trailing spaces > need to be ignored for most operations. However, I need to concatenate it > with another (literal) string and need the spaces to be significant in that > operation. The ONLY WAY I could find to do it in v9.0.1 was (represented > in the following function): > > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT > RETURNS NULL ON NULL INPUT > IMMUTABLE > LANGUAGE SQL AS $SQL$ > SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) > $SQL$; > > And then of course I write: > > SELECT padded( zzz ) || '/' || ... > > Is there a better way? > > nic=# SELECT ''::char(6) || ''; ?column? -- (1 row) Time: 2.710 ms nic=# SELECT ''::char(6)::cstring || ''; ?column? (1 row) regards Pavel Stehule -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trailing spaces - is there an easier way?
On Thu, Nov 04, 2010 at 08:42:21PM +0100, Pavel Stehule wrote: > 2010/11/4 Dean Gibson (DB Administrator) : > > I'm sure this has been asked before, but I couldn't find it: > > > > I have a "zzz CHAR (8)" field.?? It needs to be CHAR because trailing spaces > > need to be ignored for most operations.?? However, I need to concatenate it > > with another (literal) string and need the spaces to be significant in that > > operation.?? The ONLY WAY I could find to do it?? in v9.0.1 was (represented > > in the following function): > > > > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT > > ?? RETURNS NULL ON NULL INPUT > > ?? IMMUTABLE > > ?? LANGUAGE SQL AS $SQL$ > > ?? SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) > > ?? $SQL$; > > > > And then of course I write: > > > > SELECT padded( zzz ) || '/' || ... > > > > Is there a better way? > > > > > > nic=# SELECT ''::char(6) || ''; > ?column? > -- > > (1 row) > > Time: 2.710 ms > nic=# SELECT ''::char(6)::cstring || ''; > ?column? > > > (1 row) > > > regards > > Pavel Stehule > Here is a link to some useful other pseudo types: http://www.postgresql.org/docs/9.0/interactive/datatype-pseudo.html Regards, Ken -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] I'm stuck - I just can't get this small FUNCT to run!
Thank you both. I stared and stared and stared, and apparently I needed to take a walk around the building I have repeatedly asked about an upgrade, but as you mentioned Tom, it's a compatibility issue and concern that remote programmers might end out making more work for themselves. <> If I did a local install of PG 8.X (which is what I hear we'll be going to), not connected to the real DB, would it be functional enough to give me the better messages? Again, thank you both, Ralph --- bricklen wrote: You appear to be missing a trailing semi-colons. Here: vFieldName= ''offer_'' || ''$1'' and here: vBusID=daRec.bus_id --- Ralph Smith writes: > I'm also stuck on 7.4 for at least a few more months, but that's not > part of the problem. ... well, actually, it is; because newer versions give considerably more helpful syntax error messages. I tried plugging this function into a current version, and got syntax error at or near "daRec" LINE 34: FOR daRec IN SELECT * FROM temp_salesimport WHERE offer_... ^ which was at least close enough to the problem (the missing semi on the previous line) to be of some use, unlike the error pointing at line 92. Similarly, after fixing the first missing semi I got ERROR: syntax error at or near "select" LINE 38: select into vBusOwnerID businessownerid from business ... ^ which is just after the other one. If you aren't in a position to move your production DB yet, you might at least consider using a newer version for development. That has its own hazards of course, like accidentally using features that don't exist in 7.4, but it could save you a lot of time in cases like this. regards, tom lane -- Ralph _
