Re: [SQL] Documenting a DB schema
Hi
You may try this.
CREATE TYPE tabela_estrutura AS
(esquema text,
tabela text,
campo text,
tipo text,
valor text,
autoincremento boolean);
ALTER TYPE tabela_estrutura OWNER TO postgres;
CREATE OR REPLACE FUNCTION dados_tabela(character varying)
RETURNS SETOF tabela_estrutura AS
$BODY$
DECLARE
r 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.adsrc for 128) FROM
pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnumAND
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 'Table not found', vTabela;
END IF;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION dados_tabela(character varying) OWNER TO postgres;
2008/3/4, Shahaf Abileah <[EMAIL PROTECTED]>:
>
> I'm looking for a systematic way to document the schema for the database
> behind our website (www.redfin.com), so that the developers using this
> database have a better idea what all the tables and columns mean and what
> data to expect. Any recommendations?
>
>
>
> It would be great if the documentation could be kept as close to the code
> as possible – that way we stand a chance of keeping it up to date. So, in
> the same way that Java docs go right there on top of the class or method
> definitions, it would be great if I could attach my comments to the table
> definitions. It looks like MySQL has that kind of capability:
>
>
>
> create table table_with_comments(a int comment 'this is column
> a...');
>
>
>
> (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html)
>
>
>
> However, Postgres doesn't support the "comment" keyword. Is there an
> alternative?
>
>
>
> Thanks,
>
>
>
> --S
>
>
>
> *Shahaf Abileah *|* Lead Software Developer *
>
> [EMAIL PROTECTED] | tel: 206.859.2869 | fax: 877.733.3469
>
> Redfin Corporation
> 710 2nd Ave
> Suite 600
> Seattle, WA 98104
>
>
>
[SQL] finding columns that have three or fewer distinct characters
I've got an interesting one...I'm trying to find columns that have three or fewer distinct characters (for example, "aa"). Wondering if I need to write a function or if someone has an idea how to do it with built in functions and/or pattern matching? I think the thing to do would be to lowercase everything, then remove all duplicate chars and spaces, then use length() on that, but it's not obvious to me how I might remove the duplicate chars with the pattern matching support in the docs. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] finding columns that have three or fewer distinct characters
Jeff Frost wrote:
I've got an interesting one...I'm trying to find columns that have three
or fewer distinct characters (for example, "aa"). Wondering if
I need to write a function or if someone has an idea how to do it with
built in functions and/or pattern matching?
I think the thing to do would be to lowercase everything, then remove
all duplicate chars and spaces, then use length() on that, but it's not
obvious to me how I might remove the duplicate chars with the pattern
matching support in the docs.
It's interesting, indeed.
Here's how you might do it with a PL/Perl function. :)
CREATE OR REPLACE FUNCTION remove_duplicates(TEXT) RETURNS TEXT AS
$$
my ($text) = @_;
while ($text =~ s/(.)(.*)\1/$1$2/g != 0) {};
return $text;
$$ LANGUAGE plperl;
cww=# SELECT
remove_duplicates('[EMAIL PROTECTED]');
remove_duplicates
---
[EMAIL PROTECTED]
(1 row)
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] using copy from in function
At 03:20 AM 3/5/2008, [EMAIL PROTECTED] wrote: Date: Wed, 5 Mar 2008 01:51:19 +0300 From: "Yura Gal" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: using copy from in function Message-ID: <[EMAIL PROTECTED]> I'm trying to automate import data using CORY FROM. For this purpose I wrote plpgsql function. As my postgres works on windows vista I need to use E'' syntax for path-to-file. This syntax works fine in SQL queries like: COPY table FROM E'path_to_file_with_double_backslashes'; [snip] _file := $$c:\folder1\folder2\$$ || _chrom || '.txt'; Hi, I'm not sure if this is related, but I have had terrible trouble using "\" marks for paths in WinXP.. I have found surprisingly that "/" work and don't cause any parsing problems. Also, I believe that if you use syntax like: '/folder1/folder2/' || _chrom || '.txt' (i.e. leaving off the "c:" part too), you may find that everything just works a little cleaner / fewer unexpected surprises. Like I said, I don't know if this is your issue (and Vista), but it's been my experience with WinXP and file paths in Postgresql. Best, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] finding columns that have three or fewer distinct characters
Jeff Frost escreveu:
I've got an interesting one...I'm trying to find columns that have three
or fewer distinct characters (for example, "aa"). Wondering if
I need to write a function or if someone has an idea how to do it with
built in functions and/or pattern matching?
I think the thing to do would be to lowercase everything, then remove
all duplicate chars and spaces, then use length() on that, but it's not
obvious to me how I might remove the duplicate chars with the pattern
matching support in the docs.
Try:
SELECT
array_to_string(array(
SELECT DISTINCT lower(substr('aabbcdddef AB',i,1))
FROM generate_series(1,length('aabbcdddef AB'))
s(i))
, '');
Osvaldo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] finding columns that have three or fewer distinct characters
On Wed, 5 Mar 2008, Osvaldo Rosario Kussama wrote:
Jeff Frost escreveu:
I've got an interesting one...I'm trying to find columns that have three or
fewer distinct characters (for example, "aa"). Wondering if I need
to write a function or if someone has an idea how to do it with built in
functions and/or pattern matching?
I think the thing to do would be to lowercase everything, then remove all
duplicate chars and spaces, then use length() on that, but it's not obvious
to me how I might remove the duplicate chars with the pattern matching
support in the docs.
Try:
SELECT
array_to_string(array(
SELECT DISTINCT lower(substr('aabbcdddef AB',i,1))
FROM generate_series(1,length('aabbcdddef AB'))
s(i))
, '');
Osvaldo, that appears to work perfectly! Thank you much!
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
[SQL] Bit string help, please
Hey PostgreSQL Gurus, I am experimenting with PostgreSQL bit strings to see if they might help with some performance issues I am having. I added a "bit varying" column to one of my tables. I have a PL/pgSQL function with an insert statement into this table. For the bit varying column I would like to insert a bit string of length n with the highest order bit being 1 and all the other bits being 0. Given a table definition: create table table1 ( a int, b bit varying ); I hoped I could use the feature where casting right pads 0s to do something like this: insert into table1 values( DEFAULT, B'1'::bit( n ) ); Where n is one of the parameters to the PL/pgSQL function, but that doesn't work. PostgreSQL doesn't like having a variable for the bit string length. Does anyone have any ideas how I could achieve this? Thanks, Ed Tyrrill -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
[SQL] RETURN QUERY generates error
Here is my function:
CREATE OR REPLACE FUNCTION "hg18"."get_genomeseq" (_chr varchar,
_byblocks boolean, _starts integer [], _ends integer []) RETURNS SETOF
text AS
$body$
DECLARE
_startinteger;
_end integer;
_sequence text[];
_seq50RECORD;
_seq text;
_qtext;
BEGIN
FOR i IN 1..array_upper(_starts, 1) LOOP
_start := _starts[i];
_end:= _ends[i];
_q :=
'SELECT start, sequence ' ||
'FROM hg18.genome ' ||
$$WHERE chr = 'chr' || '$$ || _chr::varchar || $$' $$ ||
'AND start >= floor(' || _start || '/50)*50 ' ||
'AND start < ' || _end;
--RAISE NOTICE 'Query is %', _q;
FOR _seq50 IN EXECUTE _q LOOP
IF _seq50.start < _start THEN
_sequence[i] := substring(_seq50.sequence, _start%_seq50.start);
ELSEIF _seq50.start >= _start AND _seq50.start + 49 <= _end THEN
_sequence[i] := _sequence[i] || _seq50.sequence;
ELSE
_sequence[i] := _sequence[i]
|| substring(_seq50.sequence, 1, _end%_seq50.start);
END IF;
END LOOP;
END LOOP;
IF _byblocks IS TRUE THEN
RETURN QUERY SELECT
regexp_split_to_table(array_to_string(_sequence, ','), E',');
ELSE
RETURN QUERY SELECT array_to_string(_sequence, '');
END IF;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
all is fine until the last IF. Both RETURN QUERY blocks generate
error: 'syntax error at or near SELECT ...' I feel that something
wrong with casting _sequence var but I can't figure out the exact
point.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
