Thread: Aggregates and Primary Keys

Hi,

I have this:
 create table a ( x int4 primary key, dat int4, count int4 ) ; create table b ( x int4 references a(x), count int4 ) ;
 insert into a values ( 1, 1, 10 ) ; insert into a values ( 2, 2, 20 ) ; insert into b values ( 1, 2 ) ; insert into b
values( 1, 3 ) ; insert into b values ( 2, 3 ); insert into b values ( 2, 4 );
 
 select * from a ; select * from b ;
  x | dat | count  ---+-----+-------  1 |   1 |    10  2 |   2 |    20 (2 rows)
  x | count  ---+-------  1 |     2  1 |     3  2 |     3  2 |     4 (4 rows)
 select a.x, a.dat, a.count - sum(b.count) from a, b where a.x = b.x group by a.x, a.dat, a.count ;
  x | ?column?  ---+----------  1 |        5  2 |       13 (2 rows)

My concern is with the "group by" clause.  Strictly speaking, it
shouldn't be necessary to *also* group by a.dat and a.count, since a.x
is a primary key, right?  Is there some performance loss in specifying
a.dat and a.count in the group by?  Should I be doing this some other
way?

Thanks,
-itai

I've been looking through the material I have on postgreSQL, but can't seem
to find an answer to my problem. Very simplied, my tables are something like
this:

create table news (  id serial,  story text,  publishtime timestamp
)

create table news_unpublished ( news_id
)

I wish to make rule looking something like this:
create rule newsrule as on insert to news do   if new.publishtime is not null insert into news_unpublished
values(new.id);

I.e. "On an insert to news, if new.publish is not null, insert the new
post's id into news_unpublished.

Is this possible?

Thanks

André Næss

> I.e. "On an insert to news, if new.publish is not null, insert the new
> post's id into news_unpublished.

How about:

CREATE RULE newsrule AS
ON INSERT TO news
DO INSERT INTO news_unpublished SELECT NEW.id WHERE NEW.publishtime IS NOT NULL

-itai

"André Næss" <andre.nass@student.uib.no> writes:
> I wish to make rule looking something like this:
> create rule newsrule as
>   on insert to news do
>     if new.publishtime is not null insert into news_unpublished
> values(new.id);

> I.e. "On an insert to news, if new.publish is not null, insert the new
> post's id into news_unpublished.

What you want here is a trigger, not a rule.  The closest you could come
with a rule is to copy *all* unpublished ids into news_unpublished each
time something got inserted into news.  There are applications for that
sort of thing, but this ain't it.  See the trigger examples in the
plpgsql or pltcl sections of the manual.
        regards, tom lane

Hm... I'm a true newbie when it comes to plpgsql and triggers, but I looked
through some texts and managed to come up with the following rather odd (to
me at least) behaviour:

*****

create table news ( id serial, title varchar(50), time timestamp
)

create table news_un ( news_id int
)
       Table "news_un"Attribute |  Type   | Modifier
-----------+---------+----------news_id   | integer |

create function setpublish() returns opaque as ' begin   insert into news_un select news_id_seq.last_value where
new.timeis not
 
null;   return null; end; ' language 'plpgsql';

create trigger newstrigger after insert on news for each row execute
procedure setpublish();

*****
Attempting to do an insert to news:

testruledb=# insert into news (title, time) values('Test', now());
INSERT 24028 1
testruledb=# select * from news; select * from news_un;id | title |          time
----+-------+------------------------48 | Test  | 2000-07-27 19:20:24+02
(1 row)
news_id
---------     47     48
(2 rows)

I also tried setting time to null:

testruledb=# insert into news (title) values('Test2');
INSERT 24031 1
testruledb=# select * from news; select * from news_un;id | title | time
----+-------+------50 | Test2 |
(1 row)
news_id
---------     49
(1 row)

There's obviously something about triggers and functions I don't understand,
any help would be greatly appreciated.

Thanks

André Næss


----- Original Message -----
> "André Næss" <andre.nass@student.uib.no> writes:
> > I wish to make rule looking something like this:
> > create rule newsrule as
> >   on insert to news do
> >     if new.publishtime is not null insert into news_unpublished
> > values(new.id);
>
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
>
> What you want here is a trigger, not a rule.  The closest you could come
> with a rule is to copy *all* unpublished ids into news_unpublished each
> time something got inserted into news.  There are applications for that
> sort of thing, but this ain't it.  See the trigger examples in the
> plpgsql or pltcl sections of the manual.
>
> regards, tom lane
>

Ooops... seems I had a rule tied to my news table which caused the
malfunction, sorry if I wasted anyone's time :(

André Næss


----- Original Message -----
From: "André Næss" <andre.nass@student.uib.no>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 27, 2000 7:21 PM
Subject: Re: [SQL] Conditional rule?


> Hm... I'm a true newbie when it comes to plpgsql and triggers, but I
looked
> through some texts and managed to come up with the following rather odd
(to
> me at least) behaviour:
>
> *****
>
> create table news (
>   id serial,
>   title varchar(50),
>   time timestamp
> )
>
> create table news_un (
>   news_id int
> )
>
>         Table "news_un"
>  Attribute |  Type   | Modifier
> -----------+---------+----------
>  news_id   | integer |
>
> create function setpublish() returns opaque as '
>   begin
>     insert into news_un select news_id_seq.last_value where new.time is
not
> null;
>     return null;
>   end;
>   '
>   language 'plpgsql';
>
> create trigger newstrigger after insert on news for each row execute
> procedure setpublish();
>
> *****
> Attempting to do an insert to news:
>
> testruledb=# insert into news (title, time) values('Test', now());
> INSERT 24028 1
> testruledb=# select * from news; select * from news_un;
>  id | title |          time
> ----+-------+------------------------
>  48 | Test  | 2000-07-27 19:20:24+02
> (1 row)
>
>  news_id
> ---------
>       47
>       48
> (2 rows)
>
> I also tried setting time to null:
>
> testruledb=# insert into news (title) values('Test2');
> INSERT 24031 1
> testruledb=# select * from news; select * from news_un;
>  id | title | time
> ----+-------+------
>  50 | Test2 |
> (1 row)
>
>  news_id
> ---------
>       49
> (1 row)
>
> There's obviously something about triggers and functions I don't
understand,
> any help would be greatly appreciated.
>
> Thanks
>
> André Næss
>
>
> ----- Original Message -----
> > "André Næss" <andre.nass@student.uib.no> writes:
> > > I wish to make rule looking something like this:
> > > create rule newsrule as
> > >   on insert to news do
> > >     if new.publishtime is not null insert into news_unpublished
> > > values(new.id);
> >
> > > I.e. "On an insert to news, if new.publish is not null, insert the new
> > > post's id into news_unpublished.
> >
> > What you want here is a trigger, not a rule.  The closest you could come
> > with a rule is to copy *all* unpublished ids into news_unpublished each
> > time something got inserted into news.  There are applications for that
> > sort of thing, but this ain't it.  See the trigger examples in the
> > plpgsql or pltcl sections of the manual.
> >
> > regards, tom lane
> >
>
>
>

On Thu, 27 Jul 2000, Andr� N�ss wrote:
> I've been looking through the material I have on postgreSQL, but can't seem
> to find an answer to my problem. Very simplied, my tables are something like
> this:
> 
> create table news (
>    id serial,
>    story text,
>    publishtime timestamp
> )
> 
> create table news_unpublished (
>   news_id
> )
> 
> I wish to make rule looking something like this:
> create rule newsrule as
>   on insert to news do
>     if new.publishtime is not null insert into news_unpublished
> values(new.id);
> 
> I.e. "On an insert to news, if new.publish is not null, insert the new
> post's id into news_unpublished.
> 
> Is this possible?
> 
> Thanks
> 
> Andr� N�ss

I think a PL/pgSQL trigger will work:

-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'HANDLER plpgsql_call_handlerLANCOMPILER 'PL/pgSQL';

CREATE FUNCTION news_trigproc RETURNS OPAQUE AS 'IF TG_OP = ''INSERT'' THEN-- unnessary IF above since this is always
calledon insert only-- but shows how can detect which OP called the trigger when-- you make a trigger handle more than
justINSERT    IF NEW.publishtime NOTNULL THEN        INSERT INTO news_unpublished VALUES (NEW.id);    END IF;    RETURN
NEW;ENDIF;
 
' LANGUAGE 'plpgsql';

CREATE TRIGGER newstriggerAFTER INSERT ON news FOR EACH ROWEXECUTE PROCEDURE news_trigproc();

--         - Robert

On Thu, 27 Jul 2000, Robert B. Easter wrote:
> On Thu, 27 Jul 2000, Andr� N�ss wrote:
> > I've been looking through the material I have on postgreSQL, but can't seem
> > to find an answer to my problem. Very simplied, my tables are something like
> > this:
> > 
> > create table news (
> >    id serial,
> >    story text,
> >    publishtime timestamp
> > )
> > 
> > create table news_unpublished (
> >   news_id
> > )
> > 
> > I wish to make rule looking something like this:
> > create rule newsrule as
> >   on insert to news do
> >     if new.publishtime is not null insert into news_unpublished
> > values(new.id);
> > 
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
> > 
> > Is this possible?
> > 
> > Thanks
> > 
> > Andr� N�ss
> 

(forgot the BEGIN/END in the function!)

I think a PL/pgSQL trigger will work:

-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'HANDLER plpgsql_call_handlerLANCOMPILER 'PL/pgSQL';

CREATE FUNCTION news_trigproc RETURNS OPAQUE AS '
BEGINIF TG_OP = ''INSERT'' THEN-- unnessary IF above since this is always called on insert only-- but shows how can
detectwhich OP called the trigger when-- you make a trigger handle more than just INSERT    IF NEW.publishtime NOTNULL
THEN       INSERT INTO news_unpublished VALUES (NEW.id);    END IF;    RETURN NEW;END IF;
 
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER newstriggerAFTER INSERT ON news FOR EACH ROWEXECUTE PROCEDURE news_trigproc();

--         - Robert

Tom Lane wrote:
> "André Næss" <andre.nass@student.uib.no> writes:
> > I wish to make rule looking something like this:
> > create rule newsrule as
> >   on insert to news do
> >     if new.publishtime is not null insert into news_unpublished
> > values(new.id);
>
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
>
> What you want here is a trigger, not a rule.  The closest you could come
> with a rule is to copy *all* unpublished ids into news_unpublished each
> time something got inserted into news.  There are applications for that
> sort of thing, but this ain't it.  See the trigger examples in the
> plpgsql or pltcl sections of the manual.
   No. The rule
       CREATE RULE newsrule AS ON INSERT TO news           WHERE new.publishtime NOTNULL DO           INSERT INTO
news_unpublishedVALUES (new.id);
 
   should  do  the  job  perfectly.  Maybe  you want to have the   following rules too:
       CREATE RULE newsrule2 AS ON UPDATE TO news           WHERE old.publishtime ISNULL AND new.publishtime NOTNULL DO
         INSERT INTO news_unpublished VALUES (new.id);
 
       CREATE RULE newsrule3 AS ON UPDATE TO news           WHERE old.publishtime NOTNULL AND new.publishtime ISNULL DO
         DELETE FROM news_unpublished WHERE news_unpublished.id = old.id;
 
       CREATE RULE newsrule4 AS ON DELETE TO news           WHERE old.publishtime NOTNULL DO           DELETE FROM
news_unpublishedWHERE news_unpublished.id = old.id;
 
   With these four rules, all the inserts and deletes  are  done   automatically.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

----- Original Message -----
>     No. The rule
>
>         CREATE RULE newsrule AS ON INSERT TO news
>             WHERE new.publishtime NOTNULL DO
>             INSERT INTO news_unpublished VALUES (new.id);
>
>     should  do  the  job  perfectly.  Maybe  you want to have the
>     following rules too:

The following happens:
rules=# insert into news (title, time) values('Hei', now());
ERROR:  <unnamed> referential integrity violation - key referenced from
news_unpublished not found in news

Seems the data is inserted into news_unpublished first, thereby violating
the constraint I have defined for the news_id field (see below). After
removing the constraint a second problem arose; the id created for news
(serial) was 4, while the id inserted into news_unpublished was 3. So far a
trigger procedure seems to be the best solution.

>         CREATE RULE newsrule4 AS ON DELETE TO news
>             WHERE old.publishtime NOTNULL DO
>             DELETE FROM news_unpublished WHERE news_unpublished.id =
old.id;

This is also achieved by the following right? (id is the primary key for
news):
create table news_unpublished ( news_id int references news on delete cascade
);


André Næss

> >         CREATE RULE newsrule AS ON INSERT TO news
> >             WHERE new.publishtime NOTNULL DO
> >             INSERT INTO news_unpublished VALUES (new.id);
> 
> The following happens:
> rules=# insert into news (title, time) values('Hei', now());
> ERROR:  <unnamed> referential integrity violation - key referenced from
> news_unpublished not found in news

I noticed this, too.

More generally, I've found that whereas rules initially looked like a
great thing to use, in practice I have really no idea what they're
going to do.  And I find their description in the programmer's guide
confusing.  Is there any better rules tutorial out there?

-itai

Thanks for all the help so far. What I now have is the following structure:

create table b_news ( id serial primary key, title varchar(60), time timestamp
);

create table b_news_unpublished ( news_id int references news on delete cascade
);

CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS '
BEGIN
IF TG_OP = ''INSERT'' THEN IF NEW.time NOTNULL THEN   INSERT INTO b_news_unpublished VALUES (NEW.id); END IF;
END IF;
IF TG_OP = ''UPDATE'' THEN IF NEW.time NOTNULL AND OLD.time ISNULL THEN   INSERT INTO b_news_unpublished VALUES
(NEW.id);END IF; IF NEW.time ISNULL AND OLD.time NOTNULL THEN   DELETE FROM b_news_unpublished WHERE news_id=NEW.id;
ENDIF;
 
END IF;
RETURN null;
END;
' LANGUAGE 'plpgsql';

create trigger b_news_trigger
after insert or update on b_news
for each row execute procedure b_news_trigproc();

And this works as intended. There are however a few things that worries me.
First of all, I can't seem to find any way to list the trigger and the
function, they seem invisible. This is problematic because my work will be
continued by others, and allthough I will document everything I think it
should be possible to see the triggers and functions somehow...

Secondly, I miss one final idea, when a delete is performed on the
b_news_unpublished table, I would like to set up a rule or procedure that
sets the time value to null in b_news for each row that is affected by the
delete. I understand that the OLD and NEW objects are accessible only during
UPDATE or INSERT operations, so I can't quite see how to do this...

I also find it rather inelegant to use the constraint to handle DELETE
operations on news, whereas UPDATEs and INSERTs are handled by the trigger
procedure. Somehow I would like to either do all the tasks using the trigger
procedure, or using rules.

As for Itai Zukerman's comment: AOL. Good resources around triggers and
rules are very much needed!

Regards
André Næss

"André Næss" <andre.nass@student.uib.no> writes:
> Secondly, I miss one final idea, when a delete is performed on the
> b_news_unpublished table, I would like to set up a rule or procedure that
> sets the time value to null in b_news for each row that is affected by the
> delete. I understand that the OLD and NEW objects are accessible only during
> UPDATE or INSERT operations, so I can't quite see how to do this...

I might be wrong, but I thought the way it works is:INSERT: "NEW" refers to to-be-inserted rowUPDATE: "OLD" is old row
state,"NEW" is planned new row stateDELETE: "OLD" holds row to be deleted
 
        regards, tom lane

On Fri, 28 Jul 2000, Andr� N�ss wrote:
> Thanks for all the help so far. What I now have is the following structure:
> 
> create table b_news (
>   id serial primary key,
>   title varchar(60),
>   time timestamp
> );
> 
> create table b_news_unpublished (
>   news_id int references news on delete cascade
> );
> 
> CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS '
> BEGIN
> IF TG_OP = ''INSERT'' THEN
>   IF NEW.time NOTNULL THEN
>     INSERT INTO b_news_unpublished VALUES (NEW.id);
>   END IF;
> END IF;
> IF TG_OP = ''UPDATE'' THEN
>   IF NEW.time NOTNULL AND OLD.time ISNULL THEN
>     INSERT INTO b_news_unpublished VALUES (NEW.id);
>   END IF;
>   IF NEW.time ISNULL AND OLD.time NOTNULL THEN
>     DELETE FROM b_news_unpublished WHERE news_id=NEW.id;
>   END IF;
> END IF;
> RETURN null;
> END;
> ' LANGUAGE 'plpgsql';
> 
> create trigger b_news_trigger
> after insert or update on b_news
> for each row execute procedure b_news_trigproc();
> 
> And this works as intended. There are however a few things that worries me.
> First of all, I can't seem to find any way to list the trigger and the
> function, they seem invisible. This is problematic because my work will be
> continued by others, and allthough I will document everything I think it
> should be possible to see the triggers and functions somehow...
> 
> Secondly, I miss one final idea, when a delete is performed on the
> b_news_unpublished table, I would like to set up a rule or procedure that
> sets the time value to null in b_news for each row that is affected by the
> delete. I understand that the OLD and NEW objects are accessible only during
> UPDATE or INSERT operations, so I can't quite see how to do this...


OLD.* is available during TG_OP = ''DELETE'' in a trigger.

There are sometimes some referential integrity problems on DELETE when using
triggers to do things on tables that have a RI relationship.  Like, if you have
a table that REFERENCES another table ON DELETE SET NULL, and there is a DELETE
proc on the referenced table that also does some other update on those records,
then the regular trigger might happen before the contraint trigger.  In this
case, an update would have a RI problem when it updates some attribute and the
contraint is checked again - it would fail since the referenced primary key is
deleted already but foreign key has not been SET NULL yet by the contraint
trigger. I'm not sure what the rules are on the order of contraint trigger and
other trigger execution but sometimes the order isn't what you want and then
you get the problem on delete.  If it happens, like it happened to me, you
might quit using foreign keys and just program your triggers as much as you can
to do the same checks that the contraint triggers are doing but by having your
own triggers do it, you have control of the order of how things happen on
delete.

> 
> I also find it rather inelegant to use the constraint to handle DELETE
> operations on news, whereas UPDATEs and INSERTs are handled by the trigger
> procedure. Somehow I would like to either do all the tasks using the trigger
> procedure, or using rules.
> 
> As for Itai Zukerman's comment: AOL. Good resources around triggers and
> rules are very much needed!
> 
> Regards
> Andr� N�ss
--         - Robert

----- Original Message -----
> > And this works as intended. There are however a few things that worries
me.
> > First of all, I can't seem to find any way to list the trigger and the
> > function, they seem invisible. This is problematic because my work will
be
> > continued by others, and allthough I will document everything I think it
> > should be possible to see the triggers and functions somehow...
> >
> > Secondly, I miss one final idea, when a delete is performed on the
> > b_news_unpublished table, I would like to set up a rule or procedure
that
> > sets the time value to null in b_news for each row that is affected by
the
> > delete. I understand that the OLD and NEW objects are accessible only
during
> > UPDATE or INSERT operations, so I can't quite see how to do this...
>
>
> OLD.* is available during TG_OP = ''DELETE'' in a trigger.

Indeed it is, I just didn't read the manual good enough :)

> There are sometimes some referential integrity problems on DELETE when
using
> triggers to do things on tables that have a RI relationship.  Like, if you
have
> a table that REFERENCES another table ON DELETE SET NULL, and there is a
DELETE
> proc on the referenced table that also does some other update on those
records,
> then the regular trigger might happen before the contraint trigger.  In
this
> case, an update would have a RI problem when it updates some attribute and
the
> contraint is checked again - it would fail since the referenced primary
key is
> deleted already but foreign key has not been SET NULL yet by the contraint
> trigger. I'm not sure what the rules are on the order of contraint trigger
and
> other trigger execution but sometimes the order isn't what you want and
then
> you get the problem on delete.  If it happens, like it happened to me, you
> might quit using foreign keys and just program your triggers as much as
you can
> to do the same checks that the contraint triggers are doing but by having
your
> own triggers do it, you have control of the order of how things happen on
> delete.

This seems ok to me, but I still don't like the invisibility as I pointed
out above. If someone else was to continue my work how would they be able to
see the triggers and procedures? They don't show up on "\d tablename", so it
is very tempting to simply do this in PHP (i.e. managing the
news_unpublished table with PHP.) I have the same issue with constraints, as
they are invisible too.

So, it comes down to something like this; I would really like to use
constraints and trigger procedures, but in order to do so I need to keep a
very close eye on my database, this means that I might get into trouble if I
need to do some changes to this system after several months -- will I
remember all these hidden behaviours? By doing it in PHP all the behaviour
will be there in code, all visible and (hopefully) understandable to both
myself and others.

Again, I'm a postgre newbie, so there might be something essential I've
simply overlooked, but some clarification concerning this topic would be
nice.

Thanks

André Næss

On Sat, 29 Jul 2000, Andr� N�ss wrote:
> This seems ok to me, but I still don't like the invisibility as I pointed
> out above. If someone else was to continue my work how would they be able to
> see the triggers and procedures? They don't show up on "\d tablename", so it

SELECT prosrc FROM pg_proc WHERE proname = 'mytrigger';

The hidden pg_proc table contains all the info about the triggers.

Like in psql, you can do:

\o triginfo
SELECT * FROM pg_proc WHERE proname = 'mytrigger';
\o

and the output will go into file triginfo so you can view it better in a text
editor.

I haven't tested this myself yet, but I think you can UPDATE a record in
pg_proc to update the trigger prosrc.  Then, to have it take effect, you'd
have to stop and start the database since PL/pgSQL is bytecode compiled on the
first execution and cached in the running backends.


> is very tempting to simply do this in PHP (i.e. managing the
> news_unpublished table with PHP.) I have the same issue with constraints, as
> they are invisible too.
> 

You can use pg_dump -s to dump only the schema (definitions) of your database
and it will list all the table declarations, contraints, trigger procedures
etc.

> So, it comes down to something like this; I would really like to use
> constraints and trigger procedures, but in order to do so I need to keep a
> very close eye on my database, this means that I might get into trouble if I
> need to do some changes to this system after several months -- will I
> remember all these hidden behaviours? By doing it in PHP all the behaviour
> will be there in code, all visible and (hopefully) understandable to both
> myself and others.
> 

Well, the problem with implementing "business rules" in php is that you'd
have to also implement those rules/procedures in all of your scripts that you
ever make for the database.  If one script misbehaves or someone forgets
to update one of the scripts, then there are problems.  Or, its just a lot
more work to have to maintain all the scripts.  With the trigger procs, the
logic is implemented in one central place where no php scripts etc can
circumvent the rules/logic in the triggers.

PHP can provide a first level check that things are done right, but the trigger
procs in the database can provide the final check.

--         - Robert

Hello,

Sorry for the stupid posting, but..

There was a string concatenation function in MySQL:
SELECT CONCAT(first_name, " ", last_name) FROM table;
Is there a similar function in Postgres?

Certainly, it's possible to live without it, but i'd like to write as above,
doing concatenation in place.

Ok, it seems i found it now:
SELECT TEXTCAT(first_name, last_name) FROM table;
but it allows only 2 arguments, inserting " " or ' ' (space) causes
an error: attribute ' ' not found! Why postgres doesnt see it as string?

Ok, i got it, after all! It took > 30 min to write this query.  :(
SELECT textcat(textcat(first_name,text ' '),last_name) from table;

It kind of strange, but i found this function not in "user manual",
but doing "\df text".. There is some differences between function
descriptions in manual and those that \df returns..
Virtually \df returns much more functions than in manual,
and there is some differences in argument types.

May be someone knows a better, complete manual with ALL
function described and code samples?

sandis@mediaparks.lv
www.mediaparks.lv

"Sandis" <sandis@mediaparks.lv> writes:
> Ok, i got it, after all! It took > 30 min to write this query.  :(
> SELECT textcat(textcat(first_name,text ' '),last_name) from table;

It's a lot easier if you use the SQL-standard concatenation operator:

regression=# select 'foo' || 'bar';?column?
----------foobar
(1 row)

textcat() just exists to implement the operator, which is why it's
not documented separately.

Dunno why you'd need to specify the type of the constant explicitly
in this context --- there is only one textcat function, so the system
ought to be able to figure it out.
        regards, tom lane