2012/4/3 Alban Hertroys
<haramrae@gmail.com>On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
> That is right, there is no sense to use cursors here...
I think you're wrong there: The OP is querying a system table for tables of a certain name, which I expect can contain multiple rows for tables of the same name in different schema's.
Of course, that may not be applicable to the her situation.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
hmm...
if tablename variable contains schema name this function will never work, because:
quote_ident ('aa.aaa') gives "aa.aaa" what is not proper fully qualified name, should be "aa"."aaa".
So, my assumption is tablename variable contains only table name. If this is only table name, without schema name then postgre will try to truncate table only in schema where this table could be found (according to search_path parameter). It is not possible to have more then one table with the same name in the same schema.
Grace wrote:
"I tried to create function to truncate table"
this drives me to think about one table not all of them in database, but cursor statement could be misleading.
I think it is not a good idea to truncate all tables with the same name in all schemas (maybe this is Grace's intention - don't know).
BTW, tablename column of pg_catalog.pg_tables view contains only table name without schema, so this statement will NOT truncate all tables with the same name accross all schemas because of search_path.
Regards,
Bartek