
-- Storeprocedure {dados_tabela(varchar)} Versao {007} Esquema {padrao} Descricao {Retorna estrutura de uma tabela}
-- ------------------------------------------------------------

CREATE OR REPLACE FUNCTION padrao.dados_tabela(varchar(30))
RETURNS SETOF padrao.tabela_estrutura AS
'
 DECLARE
 r padrao.tabela_estrutura%ROWTYPE;
 rec RECORD;
 vTabela alias for $1;
 eSql TEXT;

 BEGIN
 eSql := ''SELECT CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) , CAST(attrs.attname AS TEXT), CAST("Type" AS TEXT), CAST("Default" AS TEXT), attrs.attnotnull
        FROM
               (SELECT c.oid, n.nspname, c.relname
               FROM pg_catalog.pg_class c
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
               WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
        JOIN
               (SELECT a.attname, a.attrelid,
               pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
                      (SELECT substring(d.adbin for 128) FROM pg_catalog.pg_attrdef d
                      WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
               as "Default", a.attnotnull, a.attnum
               FROM pg_catalog.pg_attribute a
               WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
        ON (attrs.attrelid = rel.oid )
        WHERE relname LIKE ''''%'' || vTabela || ''%''''
        ORDER BY attrs.attnum'';
FOR r IN EXECUTE eSql
 LOOP
 RETURN NEXT r;
END LOOP;
 IF NOT FOUND THEN
        RAISE EXCEPTION ''Tabela % nao encontrada'', vTabela;
 END IF;
 RETURN;
 END
 '
 LANGUAGE 'plpgsql';


-- StoreProcedure {gravalogin()} Versao {02} Esquema {padrao} Descricao {Grava login}
-- -----------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION padrao.gravalogin(VARCHAR, VARCHAR, VARCHAR, VARCHAR)
  RETURNS bool AS
  $BODY$
  DECLARE
 	p_usuario             alias for $1;
	p_esquema             alias for $2;
	p_localhost           alias for $3;
    p_localhostuser       alias for $4;
 	v_retorna             boolean;
  
  BEGIN
        LOOP
           UPDATE padrao.login
              SET conexaoid           = pg_backend_pid(),
				  usuario             = p_usuario,
				  esquema             = p_esquema,
				  localhost           = p_localhost,
				  localhostuser       = p_localhostuser,
				  data_login          = current_timestamp
            WHERE conexaoid           = pg_backend_pid();

            EXIT  WHEN found;

            BEGIN
                INSERT INTO padrao.login
                          ( conexaoid,
						    usuario,
						    esquema,
						    localhost,
						    localhostuser,
						  	data_login )
                     VALUES(pg_backend_pid(),      
							p_usuario,               
							p_esquema,         
							p_localhost,        
							p_localhostuser,              
							current_timestamp);
                EXIT ;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- do nothing
                      v_retorna = false;
            END;
        END LOOP;
  
   RETURN v_retorna;
  END
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE;

  

