# Even if the table field type is CHAR, we use PG_VARCHAR, because PG_CHAR is for a single character, and if we used it, it would truncate the data to one character.
# If we have a cache, use it instead of reprocessing it.
if( !%$fields|| !%$struct|| !%$default)
{
# my $query = "SELECT * FROM information_schema.columns WHERE table_name = ?";
# my $query = <<EOT;
# SELECT
# pg_tables.schemaname as "schema_name"
# ,pg_tables.tablename as "table_name"
# ,CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "table_type"
# ,pg_attribute.attname AS field
# ,pg_attribute.attnum as field_num
# ,format_type(pg_attribute.atttypid, NULL) AS "type"
# ,pg_attribute.atttypmod AS len
# ,(SELECT col_description(pg_attribute.attrelid, pg_attribute.attnum)) AS comment
# ,CASE pg_attribute.attnotnull WHEN false THEN 1 ELSE 0 END AS "is_nullable"
# ,pg_constraint.conname AS "key"
# ,pc2.conname AS foreign_key
# ,(SELECT pg_attrdef.adsrc FROM pg_attrdef
# WHERE pg_attrdef.adrelid = pg_class.oid
# AND pg_attrdef.adnum = pg_attribute.attnum) AS "default"
# FROM pg_tables, pg_class
# JOIN pg_attribute ON pg_class.oid = pg_attribute.attrelid
# AND pg_attribute.attnum > 0
# LEFT JOIN pg_constraint ON pg_constraint.contype = 'p'::"char"
# AND pg_constraint.conrelid = pg_class.oid AND
# (pg_attribute.attnum = ANY (pg_constraint.conkey))
# LEFT JOIN pg_constraint AS pc2 ON pc2.contype = 'f'::"char"
# AND pc2.conrelid = pg_class.oid
# AND (pg_attribute.attnum = ANY (pc2.conkey))
# WHERE pg_class.relname = pg_tables.tablename
# AND pg_attribute.atttypid <> 0::oid
# AND tablename = ?
# ORDER BY field_num ASC
# EOT
my$query;
if( $self->database_object->version <= 10 )
{
$query= <<EOT;
SELECT
n.nspname AS "schema_name"
,c.relname AS "table_name"
,CASE c.relkind WHEN 'r'THEN 'table'WHEN 'v'THEN 'view'WHEN 'm'THEN 'materialized view'WHEN 's'THEN 'special'WHEN 'f'THEN 'foreign table'WHEN 'p'THEN 'table'END as "table_type"
,a.attname AS "field"
,a.attnum AS "field_num"
,pg_catalog.format_type(a.atttypid,a.atttypmod) AS "type"
,CASE a.attnotnull WHEN false THEN TRUE ELSE FALSE END AS "is_nullable"
,(SELECT pg_attrdef.adsrc FROM pg_attrdef
WHERE pg_attrdef.adrelid = c.oid
AND pg_attrdef.adnum = a.attnum) AS "default"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = ? AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
EOT
}
else
{
$query= <<EOT;
SELECT
n.nspname AS "schema_name"
,c.relname AS "table_name"
,CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "table_type"
,a.attname AS "field"
,a.attnum AS "field_num"
,pg_catalog.format_type(a.atttypid,a.atttypmod) AS "type"
,CASE a.attnotnull WHEN false THEN TRUE ELSE FALSE END AS "is_nullable"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = ? AND a.attnum > 0 AND NOT a.attisdropped
return( $self->error( "Error while preparing query to get table '$table' columns specification: ", $self->database_object->errstr() ) );
$sth->execute( $table) ||
return( $self->error( "Error while executing query to get table '$table' columns specification: ", $sth->errstr() ) );
my@primary= ();
my$ref= '';
my$c= 0;
my$type_convert=
{
'character varying'=> 'varchar',
# Even if the table field type is CHAR, we use PG_VARCHAR, because PG_CHAR is for a single character, and if we used it, it would truncate the data to one character.
This is a PostgreSQL table object class. It inherits from L<DB::Object::Tables>
=head1 METHODS
=head2 create
This creates a table.
It takes some array reference data containing the columns definitions, some optional parameters and a statement handler.
If a statement handler is provided, then no need to provide an array reference of columns definition. The columns definition will be taken from the statement handler. However, at least either one of them needs to be provided to set the columns definition.
Possible parameters are:
=over 4
=item I<comment>
=item I<inherits>
Takes the name of another table to inherit from
=item I<on commit>
=item I<tablespace>
=item I<temporary>
If provided, this will create a temporary table.
=item I<with oids>
If true, this will enable table oid
=item I<without oids>
If true, this will disable table oid
=back
This will return an error if the table already exists, so best to check beforehand with L</exists>.
Upon success, it will return the new statement to create the table. However, if L</create> is called in void context, then the statement is executed right away and returned.
=head2 create_info
This returns the create info for the current table object as a string representing the sql script necessary to recreate the table.
=head2 disable_trigger
my $sth = $tbl->disable_trigger;
my $sth = $tbl->disable_trigger( all => 1 );
my $sth = $tbl->disable_trigger( name => 'my_trigger' );
Provided with some optional parameters and this will return a statement handler to disable all triggers or a given trigger on the table.
If it is called in void context, then the statement is executed immediately and returned, otherwise it is just returned.
If false, this will enable only the user triggers, i.e. not including the system ones.
=item I<name>
If a trigger name is provided, it will be used to specifically enable this trigger.
=back
=head2 exists
Returns true if the current table exists, or false otherwise.
=head2 lock
This will prepare a query to lock the table and return the statement handler. If it is called in void context, the statement handler returned is executed immediately.
It takes an optional lock type and an optional C<NOWAIT> parameter.