Обсуждение: Query output into a space delimited/location sensitive file

What would the command be to have a query result be put into a
location/space sensitive file [position 1 through 5 would be one thing
where position 6 through 10 would be the next field, 11 through 16
another, etc]?  Is this even possible with Postgres?

Ubence Quevedo wrote:
> What would the command be to have a query result be put into a
> location/space sensitive file [position 1 through 5 would be one thing
> where position 6 through 10 would be the next field, 11 through 16
> another, etc]?  Is this even possible with Postgres?

Not possible --- many data types are of variable length.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Feb 16, 2008 5:29 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Ubence Quevedo wrote:
> > What would the command be to have a query result be put into a
> > location/space sensitive file [position 1 through 5 would be one thing
> > where position 6 through 10 would be the next field, 11 through 16
> > another, etc]?  Is this even possible with Postgres?
>
> Not possible --- many data types are of variable length.

I think the OP was just looking at processing fixed with records into
pgsql.  If that's the case you can make a load table with one text
field and select into from it with something like substring(field,1,5)
etc...

Scott, you are exactly right.  I am looking to take various data in
multiple tables and create an output file delimited into specific
sections.  I'll look more into the proper usage of select into as well
as the substring/field example you have given below.

Thanx!

On Feb 16, 2008, at 04:12 PM, Scott Marlowe wrote:

> On Feb 16, 2008 5:29 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> Ubence Quevedo wrote:
>>> What would the command be to have a query result be put into a
>>> location/space sensitive file [position 1 through 5 would be one
>>> thing
>>> where position 6 through 10 would be the next field, 11 through 16
>>> another, etc]?  Is this even possible with Postgres?
>>
>> Not possible --- many data types are of variable length.
>
> I think the OP was just looking at processing fixed with records into
> pgsql.  If that's the case you can make a load table with one text
> field and select into from it with something like substring(field,1,5)
> etc...

On Feb 16, 2008 7:19 PM, Ubence Quevedo <r0d3nt@pacbell.net> wrote:
> Scott, you are exactly right.  I am looking to take various data in
> multiple tables and create an output file delimited into specific
> sections.  I'll look more into the proper usage of select into as well
> as the substring/field example you have given below.

Then you'll be wanting lpad or rpad to make the fixed widths.  It's
pretty straight ahead.

On Fri, 15 Feb 2008, Ubence Quevedo wrote:

> What would the command be to have a query result be put into a location/space
> sensitive file [position 1 through 5 would be one thing where position 6
> through 10 would be the next field, 11 through 16 another, etc]?  Is this
> even possible with Postgres?

Check out string operators like rpad and maybe even overlay in
http://www.postgresql.org/docs/current/static/functions-string.html , the
date/number formatting operations in
http://www.postgresql.org/docs/current/static/functions-formatting.html ,
and note that you can combine strings with || in between them.

You can do something like this:

select rpad(field1,5) || rpad(field2,5) || rpad(field3,5) from table1

To get back a string formatted the way you describe.  You may have to
tweak that to get the formatting on the fields exactly right.  Will be
kind of brute force and fragile if anything changes, but it should work if
you tinker enough.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD