Display of text fields

[Ennio-Sr]

[Sorry for possible pluri-repeat: I'm not putting up very well with the
technicalities of ... your Majordomo ... -:)]

Hi all!

[Using PG 7.2.1 on Debian/Woody, k. 2.2.22]

After a very long struggle I finally succeded in transferring my old
*.dbf file and the relating *.dbt (alias memo fields) to a pg table.
For the time being I put the memo field in a separate table having two
fields only (i.e.: n_memo integer, memo text) which can be related to
the main table in a view.

Now, the first problem is that when I do a select * from this table it
will scroll up to the end, no matter whether \x or \t or \pset pager are
set or not! [For information: if I put the text fields in the main
ex.dbf table - which has more columns - or do a select from a view
connecting the 2 tables the page control works fine].

Second problem: the display of each text field looks like this:

# ---------- quote ---------
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
col1 | Anatomical Diagrams for the use of the Art Students Arranged
with analytical Notes and drawn out by James M. Dunlop, A.R.C.A.,
...... [ cut ] ....

[Example taken from a single column table, but the result is the same
for the 2 cols table]

# ----------- unquote --------

My questions are then:

1. Is there a way to avoid displaying the '---------' lines, apart from
the \a flag ?
2. In case I put the memos in the main table, would it be possible to
control someway the display of memo-text fields? [Only some of the
records have a memo, and a referring col set to 'T', so I'm looking
for a selective instruction, such as: if ctl_memo='T' display memo,
else, display the other cols only].
3. Does the pager work better on version 7.4.3?

I realize that the version (7.2.1) I'm working with is old as compared
to the running 8.0, so your help will be particularly appreciated.
Regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

Ennio-Sr wrote:

My questions are then:

1. Is there a way to avoid displaying the '---------' lines, apart from
the \a flag ?
I don't think so, not in that layout.
2. In case I put the memos in the main table, would it be possible to
control someway the display of memo-text fields? [Only some of the
records have a memo, and a referring col set to 'T', so I'm looking
for a selective instruction, such as: if ctl_memo='T' display memo,
else, display the other cols only].
Use a view along with a CASE...END expression
3. Does the pager work better on version 7.4.3?

I'm not aware of any changes. Is the problem when you have a single
text-field that takes up too much space? If so, I'd construct my view
with a substring:

CREATE VIEW my_view AS
SELECT a,b,c,substring(long_memo_field, 1, 80)
FROM ...

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Ennio-Sr]

* Richard Huxton <de*@archonet.com> [020904, 16:54]:

Ennio-Sr wrote:

My questions are then: [ ... ]

2. In case I put the memos in the main table, would it be possible to
control someway the display of memo-text fields? [Only some of the
records have a memo, and a referring col set to 'T', so I'm looking
for a selective instruction, such as: if ctl_memo='T' display memo,
else, display the other cols only].

Use a view along with a CASE...END expression

3. Does the pager work better on version 7.4.3?

I'm not aware of any changes. Is the problem when you have a single
text-field that takes up too much space?

Not necessarily. I'm testing with a table containing a dozen text
fields, each one large from 6 to 20 lines; so, when I run a select with
no 'limit n', (I suppose) the pager doesn't know how to split the text
field in order to accomodate it to the max no. of lines the screen can
show.
e.g., if rec. no 1 has a text of 15 lines and rec. 2 has 20 lines, the
pager should be able to split rec. no. 2 into two parts (9 lines to the
first - so that it can show together with rec. no. 1 (15+9=24) - and 11
to the second.
If so, I'd construct my view
with a substring:

CREATE VIEW my_view AS
SELECT a,b,c,substring(long_memo_field, 1, 80)
FROM ...

Thank you so much, Richard! I'll search the documentation for the
solutions you suggest which I'm not yet acquainted with ...
Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ennio-Sr]

* Richard Huxton <de*@archonet.com> [020904, 16:54]:

Ennio-Sr wrote:

My questions are then:

[cut] 2. In case I put the memos in the main table, would it be possible to
control someway the display of memo-text fields? [Only some of the
records have a memo, and a referring col set to 'T', so I'm looking
for a selective instruction, such as: if ctl_memo='T' display memo,
else, display the other cols only].

Use a view along with a CASE...END expression

Further to my message of 3rd inst.
Following your suggestion and after reading some documents, I created
this sql script:

-----
SELECT scheda_ltr,
case scheda_ltr
when 'T' then
select * from bib_lt;
else
'autore, titolo, editore from bib_lt;'
end
FROM bib_lt;
-----
but the result is not what I was after: I get a list with either label
according to scheda_ltr being 'T' or not!
Is there any way, once the case is spotted, to obtain execution of the
query relating to that case, instead of just showing the label?
Of course I tried without the quotes obtaining parser error.

3. Does the pager work better on version 7.4.3?

I'm not aware of any changes. Is the problem when you have a single
text-field that takes up too much space? If so, I'd construct my view
with a substring:

CREATE VIEW my_view AS
SELECT a,b,c,substring(long_memo_field, 1, 80)
FROM ...

Tried substring(memo,1, 1400) ## I think the numbers refer to bytes,
## not rows (with 1,80 shows nothing)
but the uncontrolled scrolling is still there :-(
It's really sad, with all those possibilities offered by 'substr' !

Thanks for any help,
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

Ennio-Sr wrote:

Further to my message of 3rd inst.
Following your suggestion and after reading some documents, I created
this sql script:

-----
SELECT scheda_ltr,
case scheda_ltr
when 'T' then
select * from bib_lt;
else
'autore, titolo, editore from bib_lt;'
end
FROM bib_lt;
-----
but the result is not what I was after: I get a list with either label
according to scheda_ltr being 'T' or not!
Is there any way, once the case is spotted, to obtain execution of the
query relating to that case, instead of just showing the label?
Of course I tried without the quotes obtaining parser error.

Ah - looks like I misunderstood what you were trying to do. There is no
way to have a single query return rows with different numbers of columns
- each row must be the same.

You'd have to do something like one of the following (substitute
my_memo_column with whatever your memo field was called).

SELECT
scheda_ltr,
autore,
titolo,
editore,
CASE
WHEN scheda_ltr = 'T' THEN my_memo_column
ELSE 'n/a'
END AS my_memo_column
FROM
bib_lt;

or...

SELECT
scheda_ltr,
CASE
WHEN scheda_ltr='T' THEN autore || ' / ' || titolo || ' / ' || editore
ELSE my_memo_column
END AS merged_columns
FROM
bib_lt;
HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ennio-Sr]

* Richard Huxton <de*@archonet.com> [090904, 14:20]:

Ennio-Sr wrote:

[ ... ]

Ah - looks like I misunderstood what you were trying to do. There is no
way to have a single query return rows with different numbers of columns
- each row must be the same.

You'd have to do something like one of the following (substitute
my_memo_column with whatever your memo field was called).
[ ... ]

Thanks for your time, Richard. No, may be I was not clear enough ... :-)

I slightly modified your queries and the result gets nearer my goals,
but ...
Here is what I tried:

SELECT DISTINCT
/* despite the DISTINCT, it shows twice each matching record: once
with the memo fieldd and then without it!. Leaving out the DISTINCT,
each record is shown many times (may be as many as the number of
numbered fields, according to the CASE condition */
t0.n_prog,
t0.autore,
.........,
.........,
t0.scheda_ltr,
CASE
WHEN t0.scheda_ltr = 'T' AND t0.n_prog=t1.n_prog THEN
t1.note
ELSE 'n/a'
END AS note
FROM bib_lt t0, bidbt t1 where t0.n_prog<>0 ;
-- i.e. consider already numbered records only as they may have a
-- corresponding 'memo' (alias 'note') in the other table.
[bib_lt has 27 columns, bidbt only has n_rif & note (alias ex-dbf-memo)]
[I'm not sure yet as to whether it is better to keep 'note' in a
separate table or to incorporate it in the main one: much depends on
the possibility to get a reasonable way to read the note 'if and only
when they are there' (i.d. when 'scheda_ltr='T') as not all records have
a filled up 'note' field.]

-----------------
This works allright, apart from doubling the records, as noted above.
[I can get over the problem of superfluous hyphens showing (the '----' of
the memo field) chhosing:
\a # (unaligned) and
\f '-->: ' # (as field separator)]
-----------------------
The alternative:

SELECT DISTINCT
t0.scheda_ltr,
CASE
WHEN t0.scheda_ltr = 'T' AND t0.n_prog=t1.n_prog THEN t0.autore || ' / ' || t0.titolo || ' / ' || editore || ' / ' || t0.altre_notizie || ' / ' || t1.note
ELSE -- 'n/a'
t0.autore || ' / ' || t0.titolo || ' / ' || editore || ' / ' || t0.altre_notizie

END AS note
FROM bib_lt t0, bidbt t1 where t0.autore like '%SERAF%';

formats the records in a different way but does duplicate them as the
other one.

------------

I have no clue as to why records are being shown twice: a new reading of
the PG documentation did not help me much :-(
[BTW, can you suggest any better specific reading (with examples) on this
particular issue?]

Perhaps I'd better explain what I'm trying to do:

I have all my books registered in a *.dbf table and a file .exe
(construed a few years ago with clipper-S87) to access, add, modify
them. When I browse my file (for example, on a Dosemu console), if a
particolar record has the field 'scheda_ltr' set to 'T', I can press F2
and view/modify the relative 'memo' field.
I would like to be able to achieve a similar result with PostgreSQL; I
know I can do that with Pgaccess or like applications, but my goal is
being able to do it from a console! :-)

As it is only a few weeks since I started my testings seriously on
pgSQL my knowledge of its potentiality is still very limited and I'm
pretty sure there must be a way to that!

I've appreciated your help so far and hope you'll continue to assist me.
Best regards,
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

Ennio-Sr wrote:

Perhaps I'd better explain what I'm trying to do:

I have all my books registered in a *.dbf table and a file .exe
(construed a few years ago with clipper-S87) to access, add, modify
them. When I browse my file (for example, on a Dosemu console), if a
particolar record has the field 'scheda_ltr' set to 'T', I can press F2
and view/modify the relative 'memo' field.
I would like to be able to achieve a similar result with PostgreSQL; I
know I can do that with Pgaccess or like applications, but my goal is
being able to do it from a console! :-)

Ah - you're wasting your time with psql then. It's a find sql console
but it's not a browser. Might be worth posting a question asking if
anyone knows of a "console based database browser"

I'm not aware of any console-based database browsers, but if I was
seeking to write something similar I'd look into the "curses" package -
there are bindings for Perl and Python as well as C and examples of
usage on the web:

http://www.perldoc.com/perl5.6/pod/p...ses-with-Perl-
http://www.oreilly.com/catalog/curses/index.html
http://search.cpan.org/~wps/Curses-1...make.Curses.pm
http://www-106.ibm.com/developerwork...l?dwzone=linux
http://www.amk.ca/python/howto/curses/

It shouldn't be difficult to build an application to do what you want.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ennio-Sr]

* Richard Huxton <de*@archonet.com> [100904, 09:11]:

Ennio-Sr wrote:

[ ... ]

Ah - you're wasting your time with psql then. It's a find sql console
but it's not a browser. Might be worth posting a question asking if
anyone knows of a "console based database browser"
[ ... ]

Thanks again for your help, Richard. I'll have a look at the links you
suggest.

[OT]. P.S. WHile 'googling' around I read your post of september last
year about the "Trigger order problem". Now, I'm the last person who
may advice others on how to do things in pg but, as a matter of
curiosity, could not you solve your problem adding an extra 'flag' column
to the tables to be marked with a 'D' (for deleted), perform the summing
up and finally remove all 'D' marked records?
Anyway, I hope the 'problem' is no longer such! :-)
Cheers,
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

On Fri, 10 Sep 2004, Ennio-Sr wrote:

I slightly modified your queries and the result gets nearer my goals,
but ...
Here is what I tried:

SELECT DISTINCT
/* despite the DISTINCT, it shows twice each matching record: once
with the memo fieldd and then without it!. Leaving out the DISTINCT,
each record is shown many times (may be as many as the number of
numbered fields, according to the CASE condition */
t0.n_prog,
t0.autore,
.........,
.........,
t0.scheda_ltr,
CASE
WHEN t0.scheda_ltr = 'T' AND t0.n_prog=t1.n_prog THEN
t1.note
ELSE 'n/a'
END AS note
FROM bib_lt t0, bidbt t1 where t0.n_prog<>0 ;

As an explanation of the duplicate rows:

FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
is going to give alot of rows with basically every combination (1st row of
t0 with 1st row of t1, 1st row of t0 with 2nd row of t1, etc...). Some of
these rows will have t0.n_prog=t1.n_prog but most will not. You then
project the select list for each of those rows. The ones with 'T' are
going to get (assuming no duplicates in t0.n_prog or t1.n_prog) one row
with the note as the final field, and a bunch more with 'n/a' as it.
When you DISTINCT those, it sees that the note and 'n/a' are distinct
(well, usually) and outputs both.
If you're not using any other fields from t1, I would wonder if something
like:

SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note
FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and
t0.n_prog=t1.n_prog) where t0._nprog<>0;

would be closer to what you want from the query. The join should give
output with either t0 extended by NULLs or t0 joined by t1 dependant on
whether t0.scheda_ltr='T' and if it finds a matching row in t1.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ennio-Sr]

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 07:10]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

I slightly modified your queries and the result gets nearer my goals,
but ...
Here is what I tried:
[ ... ]

As an explanation of the duplicate rows:

FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
[ ... ]
If you're not using any other fields from t1, I would wonder if something
like:

SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note
FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and
t0.n_prog=t1.n_prog) where t0._nprog<>0;

would be closer to what you want from the query. The join should give
output with either t0 extended by NULLs or t0 joined by t1 dependant on
whether t0.scheda_ltr='T' and if it finds a matching row in t1.

Thank you Stephen, for your contribution: I'll study it in due course
.... as I'm interested to learn as much as possible ...
However, in the meantime, I think I found the solution. What helped me
was the construction of these two testing tables:

Table "foo"
Column | Type | Modifiers
--------+-------------------+-----------
a | integer |
b | character varying |
c | character varying |
has_d | character(1) |

# which I filled with:

a | b | c | has_d
---+------+--------+-------
1 | one | number | Y
2 | two | number | Y
3 | tree | name | Y
4 | blue | color | N
5 | john | person | N
(5 rows)

# and:

Table "foo_d"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
d | text |

# bearing my 'would-be' memo field:

a | d
---+----------------------------------
1 | is the first natural
2 | follows 1 in the seq of natural
3 | there are various qualities of -
(3 rows)

# Then I launched an 'nth' variant of my query:

SELECT DISTINCT
--- t0.a, t0.b, t0.c, t0.has_d, -- t1.d,
-- t0.has_d, -- ## ok, mostr prima i due 'N' e poi due volte quelli Y
-- ## se tolgo 't0.has_d', cambia ordine ma sempre 8
-- ## sono
CASE
WHEN t0.has_d = 'Y' AND t0.a=t1.a
THEN t0.a || ' - ' || t0.b || ' - ' || t0.c || ' - ' || t1.d
ELSE
CASE
WHEN t0.has_d = 'N'
THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' || t0.has_d
END
END AS "The result is: "
FROM foo t0, foo_d t1;

--------------------
# and finally:

The result is:
----------------------------------------------------
1 - one - number - is the first natural
2 - two - number - follows 1 in the seq of natural
3 - tree - name - there are various qualities of -
4 / blue / color
5 / john / person

(6 rows)

# which is exaclty what I was looking for :-)))))
# Then I re-read #9.12.1 CASE of pg 7.4 Documentation with a slight
# different syntax that I'm going to try ...
-----------------------------
As I told Richard in a previous message, I was sure the solution ought to
be there: it's a question of being patient and having time to
'experiment' ;-)
Thanks you all for the assistance.
Best regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Stephan Szabo]

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 07:10]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

I slightly modified your queries and the result gets nearer my goals,
but ...
Here is what I tried:
[ ... ]
As an explanation of the duplicate rows:

FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
[ ... ]
If you're not using any other fields from t1, I would wonder if something
like:

SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note
FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and
t0.n_prog=t1.n_prog) where t0._nprog<>0;

would be closer to what you want from the query. The join should give
output with either t0 extended by NULLs or t0 joined by t1 dependant on
whether t0.scheda_ltr='T' and if it finds a matching row in t1.

Thank you Stephen, for your contribution: I'll study it in due course
... as I'm interested to learn as much as possible ...
However, in the meantime, I think I found the solution. What helped me
was the construction of these two testing tables:

Table "foo"
Column | Type | Modifiers
--------+-------------------+-----------
a | integer |
b | character varying |
c | character varying |
has_d | character(1) |

# which I filled with:

a | b | c | has_d
---+------+--------+-------
1 | one | number | Y
2 | two | number | Y
3 | tree | name | Y
4 | blue | color | N
5 | john | person | N
(5 rows)

# and:

Table "foo_d"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
d | text |

# bearing my 'would-be' memo field:

a | d
---+----------------------------------
1 | is the first natural
2 | follows 1 in the seq of natural
3 | there are various qualities of -
(3 rows)

# Then I launched an 'nth' variant of my query:

SELECT DISTINCT
--- t0.a, t0.b, t0.c, t0.has_d, -- t1.d,
-- t0.has_d, -- ## ok, mostr prima i due 'N' e poi due volte quelli Y
-- ## se tolgo 't0.has_d', cambia ordine ma sempre 8
-- ## sono
CASE
WHEN t0.has_d = 'Y' AND t0.a=t1.a
THEN t0.a || ' - ' || t0.b || ' - ' || t0.c || ' - ' || t1.d
ELSE
CASE
WHEN t0.has_d = 'N'
THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' || t0.has_d
END
END AS "The result is: "
FROM foo t0, foo_d t1;

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.
The result is:
----------------------------------------------------
1 - one - number - is the first natural
2 - two - number - follows 1 in the seq of natural
3 - tree - name - there are various qualities of -
4 / blue / color
5 / john / person

(6 rows)

# which is exaclty what I was looking for :-)))))

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ennio-Sr]

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 09:05]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 07:10]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:
[ big cut ]

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.

Stephan,
I just tested my query on the main tables (bibl_lt and bidbt) and it
seems to work reasonably quickly (my tables are not all that large:
around 10.000 rows only!). But, if it is possible to get a better
result, why not?
So, when you say '..compared to other solutions..' are you thinking
about 'COALESCE' (which I have not studied yet) or some other type
of instruction, other than psql's?
TIA,
Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 09:05]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 07:10]:
> On Fri, 10 Sep 2004, Ennio-Sr wrote:
> [ big cut ]

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.

Stephan,
I just tested my query on the main tables (bibl_lt and bidbt) and it
seems to work reasonably quickly (my tables are not all that large:
around 10.000 rows only!). But, if it is possible to get a better
result, why not?
So, when you say '..compared to other solutions..' are you thinking
about 'COALESCE' (which I have not studied yet) or some other type
of instruction, other than psql's?

Well, I'd expect that for large tables the outer join type solution would
tend to be faster than joining every row to every other row and then using
a unique step (probably after a sort) to basically remove the ones you
don't want. If you try different solutions, you can use EXPLAIN ANALYZE to
compare query plans.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ennio-Sr]

* Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 10:39]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

> * Stephan Szabo <ss****@megazone.bigpanda.com> [100904, 07:10]:
> > On Fri, 10 Sep 2004, Ennio-Sr wrote:
> > [ big cut ]

Well, I'd expect that for large tables the outer join type solution would
tend to be faster than joining every row to every other row and then using
a unique step (probably after a sort) to basically remove the ones you
don't want. If you try different solutions, you can use EXPLAIN ANALYZE to
compare query plans.

Please forget my previous message: I've just finished trying your
'COALESCE' solution (prior to studying it ;) ) and it seems to work
greatly!

---------
SELECT
t0.a, t0.b, t0.c, COALESCE(t1.d, ' ') as note from foo t0 left OUTER
JOIN foo_d t1 on (t0.has_d = 'Y' AND t0.a=t1.a);

# the result is:

a | b | c | note
---+------+--------+----------------------------------
1 | one | number | is the first natural
2 | two | number | follows 1 in the seq of natural
3 | tree | name | there are various qualities of -
4 | blue | color |
5 | john | person |
(5 rows)
^^^
---------
Perfect, I would say :-)
Thank you again so much indeed, Stephan.
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster