• Pradžia
• JavaSCRIPT
• PHP
• PostgreSQL

SQL užklausos duomenų struktūrai gauti

Duombazių sąrašas:

	SELECT datname FROM pg_database order by datname 

* Kiti duomenys prieinami tik prisijungus prie pažymėtos duomenų bazės:

	<?php  
		$pg_connect_string = "host=localhost port=5432 dbname=".$db_name."  user=     password=     ";
	?>

Lentelių sąrašas:

	select c.relname as table_name, c.relkind as ttip FROM pg_namespace n
		INNER join pg_class c ON c.relnamespace = n.oid
		WHERE
		( c.relkind = 'r' or  c.relkind = 'v' ) AND
		n.nspname = 'public'
		order by c.relkind, c.relname;

Stulpelių pavadinimai ir tipai:

	SELECT a.attrelid, a.attname AS name, t.typname AS type, c.oid as tableoid, a.attnum as colnum ,c.oid as coid , a.attrelid as attrd ,
		   a.attlen ,
		   CASE WHEN ( a.attlen )>0 
					THEN '<span style=\"color:#404000;\"><em>'||( a.attlen )::text||'</em></span>' 
						ELSE (' ')::text END AS size,

		  CASE WHEN ( a.atttypmod - 4 )>0 
					THEN '<span style=\"color:#008080 ;\"><strong>'||( a.atttypmod - 4 )::text||'</strong></span>' 
						ELSE (' ')::text END as len,
		 --( a.atttypmod - 4 ) AS len,
		  case when a.attnotnull then 'NOT NULL' else ' ' end AS nnull ,
		  CASE a.atttypid
				WHEN 21 /*int2*/ THEN 16
				WHEN 23 /*int4*/ THEN 32
				WHEN 20 /*int8*/ THEN 64
				WHEN 1700 /*numeric*/ THEN
					CASE WHEN a.atttypmod = -1
						THEN null
						ELSE ((a.atttypmod - 4) >> 16) & 65535     
						END
				WHEN 700 /*float4*/ THEN 24 
				WHEN 701 /*float8*/ THEN 53 
				ELSE null
		END   AS numeric_precision,
		CASE 
		WHEN a.atttypid IN (21, 23, 20) THEN 0
		WHEN a.atttypid IN (1700) THEN            
			CASE 
				WHEN a.atttypmod = -1 THEN null       
				ELSE (a.atttypmod - 4) & 65535           
			END
			ELSE null
		END AS numeric_scale
   FROM pg_attribute a ,  pg_class c,  pg_type t
   WHERE  a.attrelid = c.oid AND a.atttypid = t.oid and a.attnum > 0 and not a.attisdropped  and c.relname = '$table_name'

Indeksai:

	SELECT 
	DISTINCT 
	d.indisprimary, d.indisunique, d.indisclustered, d.indisvalid,
	i.relname, c.i,
	CASE WHEN d.indkey[c.i] = 0 THEN NULL
	ELSE a.attname
	END
	FROM pg_class AS i
	JOIN pg_index AS d ON d.indexrelid = i.oid
	JOIN pg_class AS t ON t.oid = d.indrelid
	JOIN pg_attribute AS a ON (a.attrelid = t.oid)
	JOIN generate_series(0, 9) AS c(i) ON d.indkey[c.i] IN (a.attnum, 0)
	WHERE t.relname = '$table_name'
	AND t.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('public'))

Reikšmės pagal nutylėjimą:

 	SELECT  pc.relname as tablename , pat.attname as colname, pad.adsrc as default_value FROM pg_attrdef pad, pg_attribute pat, pg_class pc
	WHERE 	
     pc.oid=pat.attrelid AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum
     AND pc.relname='".$table_name."' 
    -- AND pat.attname='".$col_name."'

Antrinei raktai (Foreign Keys):

 	SELECT conname,
  			pg_catalog.pg_get_constraintdef(r.oid, true) as condef
	FROM pg_catalog.pg_constraint r
	where r.conrelid = '$table_name'::regclass AND r.contype = 'f'