Re: [SQL] backup and document views and user functions
You'll probably have to write something (a function) that pulls the data out of pg_catalog. You can get a leg up on that by connecting to psql using -E, which echoes hidden queries. If you do a \df+ on a function, you'll see the query PG uses. ex. production=# \df+ myschema.* * QUERY ** SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE WHEN p.proisagg THEN 'agg' WHEN p.proiswindow THEN 'window' WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' ELSE 'normal' END as "Type", CASE WHEN p.provolatile = 'i' THEN 'immutable' WHEN p.provolatile = 's' THEN 'stable' WHEN p.provolatile = 'v' THEN 'volatile' END as "Volatility", pg_catalog.pg_get_userbyid(p.proowner) as "Owner", l.lanname as "Language", p.prosrc as "Source code", pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang WHERE n.nspname ~ '^(myschema)$' ORDER BY 1, 2, 4; ** On Mon, Aug 30, 2010 at 2:21 PM, David Harel wrote: > Hi, > > I am looking for an easy way to backup views and functions. I want to store > them in our version control system. > > Using pgAdmin I can access them one at a time. I am looking for a better > reporting mechanism. psql shell command for such report will be just fine. > > Sorry for the lame question. I didn't find any clues on the web > .(typically, I fail to phrase the right keywords) > > -- > Thanks. > > David Harel, > > == > > Home office +972 77 7657645 > Cellular: +972 54 4534502 > Snail Mail: Amuka > D.N Merom Hagalil > 13802 > Israel > Email: harel...@ergolight-sw.com > > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] concatenate question
I think the HINT is what you need to look at. Cast both columns to text. On Tue, Dec 7, 2010 at 4:37 PM, Tony Capobianco wrote: > Here's my table: > >plsql_dw=# \d tmpsv_parent_master > Table "staging.tmpsv_parent_master" > Column |Type | Modifiers >+-+--- > memberid | numeric | > addeddate | timestamp without time zone | > sourceid | numeric | > regcomplete| numeric(1,0)| > optoutdate | date| > bouncedate | date| > websiteid | numeric | > emailbounced | numeric(2,0)| > emailok| numeric(2,0)| > emailaddress | character varying(50) | > srcwebsiteid | numeric | > srcmemberid| numeric | > sitetype | character varying | > commissionpct | numeric | > pricepermember | numeric | > acceptrate | numeric(3,2)| > mktgcenterid | numeric | > label | character varying(32) | > > >Why won't this work? >plsql_dw=# select memberid || addeddate from tmpsv_parent_master > limit >10; >ERROR: operator does not exist: numeric || timestamp without > time zone >LINE 1: select memberid || addeddate from tmpsv_parent_master > limit ... >^ >HINT: No operator matches the given name and argument type(s). > You >might need to add explicit type casts. > >Thanks. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] concatenate question
I don't know what Postgres version you're using but check out the doc related to String Functions and Operators. Cheers, Peter On Tue, Dec 7, 2010 at 4:47 PM, Tony Capobianco wrote: > Ok, that worked. Why did I need to cast both as text though? > > plsql_dw=# select memberid::text||addeddate::text from > tmpsv_parent_master limit 5; > ?column? > -- > 4005941032010-11-16 19:32:17 > 4005941952010-11-16 19:33:29 > 4005942842010-11-16 19:34:32 > 4005943492010-11-16 19:35:22 > 4005943662010-11-16 19:35:37 > (5 rows) > > Thanks. > > On Tue, 2010-12-07 at 16:43 -0500, Peter Steinheuser wrote: > > I think the HINT is what you need to look at. > > > > Cast both columns to text. > > > > On Tue, Dec 7, 2010 at 4:37 PM, Tony Capobianco > > wrote: > > Here's my table: > > > >plsql_dw=# \d tmpsv_parent_master > > Table "staging.tmpsv_parent_master" > > Column |Type | > > Modifiers > > > > +-+--- > > memberid | numeric | > > addeddate | timestamp without time zone | > > sourceid | numeric | > > regcomplete| numeric(1,0)| > > optoutdate | date| > > bouncedate | date| > > websiteid | numeric | > > emailbounced | numeric(2,0)| > > emailok| numeric(2,0)| > > emailaddress | character varying(50) | > > srcwebsiteid | numeric | > > srcmemberid| numeric | > > sitetype | character varying | > > commissionpct | numeric | > > pricepermember | numeric | > > acceptrate | numeric(3,2)| > > mktgcenterid | numeric | > > label | character varying(32) | > > > > > >Why won't this work? > >plsql_dw=# select memberid || addeddate from > > tmpsv_parent_master > > limit > >10; > >ERROR: operator does not exist: numeric || timestamp > > without > > time zone > >LINE 1: select memberid || addeddate from > > tmpsv_parent_master > > limit ... > >^ > >HINT: No operator matches the given name and argument > > type(s). > > You > >might need to add explicit type casts. > > > >Thanks. > > > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > > > > > > -- > > Peter Steinheuser > > psteinheu...@myyearbook.com > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] Getting top 2 by Category
Well, if yoi have PG 8.4 and above - select categoryid, magazineid from ( select row_number() over (partition by categoryid order by categoryid,magazineid asc) as row_number, categoryid, magazineid from magazinecategory) foo where row_number < 3; categoryid | magazineid + 3 | 2 3 | 8 4 | 10 4 | 11 (4 rows) On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: > This is probably very simple but I am drawing a blank. Do I need to create > a cursor to iterate through a table to grab the top 2 magazines per > category? Here is my table and some data . The results I need are at the > bottom. Any help would be greatly appreciated: > > > > CREATE TABLE magazinecategory > > ( > > magazinecategoryid smallint NOT NULL , > > magazineid smallint, > > categoryid smallint > > ); > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (1, 2, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (2, 8, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (3 9, 3); > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (4, 10, 4); > > > > > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (5, 11, 4); > > > > INSERT INTO magazinecategory( > > magazinecategoryid, magazineid, categoryid) > > VALUES (6, 12,4); > > > > > > > > The results I want are > > CategoryID MagazineID > > 3 2 > > 3 8 > > 4 10 > > 4 11 > > > > > > > > *Pam Ozer* > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] Getting top 2 by Category
There's probably several ways - not saying this is best/optimal. SELECT categoryid, magazineid FROM magazinecategory a WHERE ( SELECT COUNT(*) FROM magazinecategory WHERE categoryid = a.categoryid AND magazineid <= a.magazineid ) < 3 order by categoryid, magazineid; On Wed, Jan 19, 2011 at 3:11 PM, Carla wrote: > 2011/1/11 Peter Steinheuser > >> Well, if yoi have PG 8.4 and above - >> >> select categoryid, magazineid from ( >> select row_number() over (partition by categoryid order by >> categoryid,magazineid asc) as row_number, >> categoryid, magazineid from magazinecategory) foo >> where row_number < 3; >> categoryid | magazineid >> + >> >> 3 | 2 >> 3 | 8 >> 4 | 10 >> 4 | 11 >> (4 rows) >> >> > How can I do it in PG 8.3? > > >> >> >> On Tue, Jan 11, 2011 at 2:00 PM, Ozer, Pam wrote: >> >>> This is probably very simple but I am drawing a blank. Do I need to >>> create a cursor to iterate through a table to grab the top 2 magazines per >>> category? Here is my table and some data . The results I need are at the >>> bottom. Any help would be greatly appreciated: >>> >>> >>> >>> CREATE TABLE magazinecategory >>> >>> ( >>> >>> magazinecategoryid smallint NOT NULL , >>> >>> magazineid smallint, >>> >>> categoryid smallint >>> >>> ); >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (1, 2, 3); >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (2, 8, 3); >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (3 9, 3); >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (4, 10, 4); >>> >>> >>> >>> >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (5, 11, 4); >>> >>> >>> >>> INSERT INTO magazinecategory( >>> >>> magazinecategoryid, magazineid, categoryid) >>> >>> VALUES (6, 12,4); >>> >>> >>> >>> >>> >>> >>> >>> The results I want are >>> >>> CategoryID MagazineID >>> >>> 3 2 >>> >>> 3 8 >>> >>> 4 10 >>> >>> 4 11 >>> >>> >>> >>> >>> >>> >>> >>> *Pam Ozer* >>> >> >> >> >> -- >> Peter Steinheuser >> psteinheu...@myyearbook.com >> > > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1
I'm not saying this is good or best but: select distinct a.c1, array_to_string(array(select c2 from t1 as b where b.c1 = a.c1),',') from t1 as a; c1 | array_to_string +- 1 | val1,val2,val3 2 | val1 3 | val5,val6 (3 rows) On Tue, Feb 8, 2011 at 2:35 PM, Emi Lu wrote: > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > - > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] Drawing a blank on some SQL
Not tested. 1. select count(t2.activity_id), t1.activity_id from (select distinct activity_id from foo_activity) as t1, -- assumes all activities exist somewhere in table left join foo_activity t2 on (t1.activity_id = t2.activity_id) WHERE created >= '01/01/2011' and created < '01/08/2011' group by 2 order by 2; 2. -- use generate_series for simple numbering scheme select count(t2.activity_id) t1.x as 'activity_id" from generate_series(1,12) as t1(x), left join foo_activity t2 on (t1.x = t2.activity_id) WHERE created >= '01/01/2011' and created < '01/08/2011' group by 2 order by 2; On Fri, Feb 11, 2011 at 1:46 PM, Aaron Burnett wrote: > > Hi, > > I'm just drawing a blank entirely today and would appreciate some help on > this. > > The long and short; there are 12 distinct activities that need to be > queried > on a weekly basis: > > SELECT count(activity_id), activity_id > FROM foo_activity > WHERE created >= '01/01/2011' and created < '01/08/2011' > GROUP BY 2 > ORDER BY 2; > > It gives me this answer, which is correct: > > count | activity_id > ---+- > 1502 | 1 >11 | 2 > 2 | 3 > 815 | 4 > 4331 | 7 >30 | 9 > 1950 | 10 > 7 | 11 >67 | 12 > > But what I need to see is if there are no activities for the particular > activity_id that week, that it lists the count as 0 and lists the > activity_id associated like this: > > count | activity_id > ---+- > 1502 | 1 >11 | 2 > 2 | 3 > 815 | 4 > 0 | 5 > 0 | 6 > 4331 | 7 > 0 | 8 >30 | 9 > 1950 | 10 > 7 | 11 >67 | 12 > > Thanking you in advance for any help on this. The caffiene seems to be not > working well today. > > Aaron > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] Determine length of numeric field
In Oracle you never have to cast, in Postgres it's pretty common. See the doc on String Operators and Functions also: http://www.postgresql.org/docs/8.4/interactive/functions-string.html On Tue, Feb 15, 2011 at 3:48 PM, Tony Capobianco wrote: > I'm altering datatypes in several tables from numeric to integer. In > doing so, I get the following error: > > dw=# \d uniq_hits >Table "support.uniq_hits" > Column | Type | Modifiers > +-+--- > sourceid | numeric | > hitdate| date| > total | numeric | > hitdate_id | integer | > Indexes: >"uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > "support_idx" > Tablespace: "support" > > esave_dw=# alter table uniq_hits alter sourceid type int; > ERROR: integer out of range > > Sourceid should not be more than 5 digits long. I'm able to perform > this query on Oracle and would like something similar on postgres 8.4: > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > where length(sourceid) > 5); > > I haven't had much luck with the length or char_length functions on > postgres. > > Thanks. > Tony > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Peter Steinheuser psteinheu...@myyearbook.com
Re: [SQL] is there a refactor
You could also do something like:
select nspname, relname, attname
from pg_attribute a
JOIN pg_class c ON (a.attrelid = c.oid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
where a.attname ~ 'yourcolumn'
and c.relname !~ 'pg'
and n.nspname not in ('pg_catalog','information_schema')
order by 1,2,3;
I'd functionalize it.
On Tue, Apr 5, 2011 at 10:44 AM, Adrian Klaver wrote:
> On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote:
> > Hi,
> > I would like to have a simple way to retrieve information for a field
> name.
> > By that I mean have some SQL select that will return all the tables a
> field
> > name exist within a database. I did not find anything with google but of
> > course google depends on the search string.
> >
> > Thanks in advance,
> > Johnf
>
> test(5432)aklaver=>SELECT table_name from information_schema.columns where
> column_name = 'id';
> table_name
>
> user_test
> table2
> table1
> hours
> jedit_test
> topics
> t2
> stone
> serial_test
> messages
> binary_test
> user_test
> timestamp_test
> role_t
> py_test
> money_test
> lock_test
> local_1
> lang_test
> interval_test
> foob
> fooa
> fldlength
> fk_1
> default_test
> csv_null
> check_two
> check_test
> array_test
> (29 rows)
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Peter Steinheuser
psteinheu...@myyearbook.com
