Re: [SQL] How to figure out when was a table created
Well, in certain filesystems you can have the birth time (like ufs2) stored in the inode struct. So you find the file name in your $PGDATA/base directory using the oid of your table (in pg_class), and then you open that file with stat (2) or utimes (2) (or from perl) to read creation data. All that apply for FreeBSD, see if creation time is supported in ext2/3. On Thu, 2 Oct 2003, David B wrote: > Hi folks, > > I posted this question a few days ago and got no response so I guess it > cannot be done (surprising!) > So that leaves me with my business problem. > > We create a table for each days activity. > After N days (typically 7 days) we can drop the table. > The table name is not known so cannot force business to make tablename > something like mydata_MMDDYY > > I'd like to be able to do something like: > SELECT tablename > FROM pg_??? > WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days', > 'dd/mm/yy' ) > > Any suggestions? > > --- Prior msg was: > > Folks, > > I have a list of tables for which I want to get the date they were > created...and if possible the date last updateded. > > I suspect there is a pg_??? table that can answer this question but I don't > know what it is and I cannot find it mentioned in any docs. > > Any suggestions...tia > -D > > p.s. Love this forum! > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] HeapTuple->t_tableOid==0 after SPI_exec
Hi,
i notice that when HeapTuple data are populated by a trigger
then the table oid can be retrieved from HeapTuple->t_tableOid.
When HeapTuple is populated by
SPI_exec("select * from foobar when id=667");
tuple = SPI_tuptable->tvals[0] (id is PK and row with 667 exists)
then tuple->t_tableOid is always 0.
Is it a known issue??
Am i missing something?
-Achilleus
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] HeapTuple->t_tableOid==0 after SPI_exec
On Fri, 3 Oct 2003, Tom Lane wrote: > > I think in 7.4 there may be an optimization that skips the tuple > projection step in this particular case, but if you can in fact see > t_tableOid in 7.4, it'd be an implementation artifact rather than > something we will promise to support in future. The correct way if you > want to see tableoid is to select it: > > select tableoid,* from foobar where ... > > and then extract it from the result using the usual field-access > routines. Thanx. P.S. are there gonna be changes in SPI or internal structs in 7.4? > > regards, tom lane > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Object description at Client Window
On Fri, 17 Oct 2003, Kumar wrote:
> But I have get into another problem. While I execute the following command I
> could get the result as U can see below
>
> etgsuite=# SELECT a.attname,format_type(a.atttypid, a.atttypmod),
> a.attnotnull, a.atthasd
> ef, a.attnum
> FROM pg_class c, pg_attribute a
> WHERE c.relname = 'companies'
> AND a.attnum > 0 AND a.attrelid = c.oid
> ORDER BY a.attnum;
> attname | format_type | attnotnull | atthasdef |
> attnum
> +-++---+
>
> company_id | bigint | t | t |
> 1
> name | character varying(100) | f | f |
> 2
> website| character varying(50) | f | f |
> 3
> address1 | character varying(100) | f | f |
> 4
> address2 | character varying(100) | f | f |
> 5
> city | character varying(50) | f | f |
> 6
> state | character varying(50) | t | f |
> 7
> postal_code| character varying(30) | t | f |
> 8
> country| character varying(50) | t | f |
> 9
> account_manager_id | bigint | t | f |
> 10
> primary_contact_id | bigint | t | f |
> 11
> company_type_id| bigint | t | f |
> 12
> status_flag| bigint | f | f |
> 13
> lead_source| bigint | f | f |
> 14
> lead_date | timestamp without time zone | f | f |
> 15
> industry_type | bigint | f | f |
> 16
> rec_modifier_id| bigint | t | f |
> 17
> rec_created_date | timestamp without time zone | t | f |
>18
> rec_modified_date | timestamp without time zone | f | f |
> 19
> rec_deleted_flag | character(1)| t | f |
> 20
> (20 rows)
>
> So I tried to create a plpgsql function as follows to return these for all
> the table name. So I have created a function like this
>
> CREATE OR REPLACE FUNCTION public.desc_table(varchar)
> RETURNS refcursor AS
> 'DECLARE
>
> ref REFCURSOR ;
> p_tablename ALIAS FOR $1;
>
> BEGIN
> OPEN ref FOR
> SELECT a.attname,
> format_type(a.atttypid, a.atttypmod),
> a.attnotnull,
> a.atthasdef,
> a.attnum
> FROM pg_class c, pg_attribute a
> WHERE c.relname = p_tablename
> AND a.attnum > 0
> AND a.attrelid = c.oid
> ORDER BY a.attnum;
>
> RETURN ref;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> While trying to execute this
> select desc_table('companies');
>
> I got the following error.
> WARNING: Error occurred while executing PL/pgSQL function desc_table
> WARNING: line 7 at open
>
> ERROR: Unable to identify an operator '=' for types 'name' and 'character
> varying'
> You will have to retype this query using an explicit cast
replace
WHERE c.relname = p_tablename
with
WHERE c.relname::varchar = p_tablename
>
> I have write many functions of the same structure and executed with out
> problems. Where I am doing wrong here.
>
> Please shed some light.
>
> Regards
> Kumar
>
> - Original Message -
> From: "Richard Huxton" <[EMAIL PROTECTED]>
> To: "Kumar" <[EMAIL PROTECTED]>; "Jordan S. Jones" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Friday, October 17, 2003 2:54 PM
> Subject: Re: [SQL] Object description at Client Window
>
>
> > On Friday 17 October 2003 09:44, Kumar wrote:
> > > Hi ,
> > >
> > > Jordan, thanks for ur reply. But I am not asking that.
> > >
> > > I want to get all the column names of any table at the PgAdmin3 SQL
> Window.
> > > To make it more clear, actually i wanted to send the table name as the
> > > input parameter for a function and expecting the column names, data
> types,
> > > etc as the output.
> > >
> > > Is there any command or any system table from that I could query the
> column
> > > names of a table (other than \d table name at the command prompt).
> >
> > Try what the man said. Start psql with -E and issue \d mytable and it will
> > show you the SQL it uses to produce the table's details.
> >
> > --
> > Richard Huxton
> > Archonet Ltd
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
--
-Achilleus
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question regarding triggers
Why dont you try to write your trigger in C? On Mon, 20 Oct 2003, Dmitri Fuerle wrote: > >I'm writing a trigger but running into problems. My problem is that I can not > determine anyway to tell what fields are in the *new* record. Without knowing what > fields are there I get runtime errors if that's not what is being updated example: > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > begin > If new.shipment_type_id = 4 then > --do something > end if; > return new; > end; > > in the above example everything works if shipment_type_id is being updated. If it > is not I will receive a run-time error because shipment_type_id is not part of the > record new. > > Please help, > Dmitri > > > > > - > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Question regarding triggers
On Mon, 20 Oct 2003, A.Bhuvaneswaran wrote: > > Why dont you try to write your trigger in C? > > Hi, one cannot write triggered procedures in C. Currently, it can only be > written in plpsgql. Where did you get that impression from? Do an SELECT tgrelid,tgfoid,proname from pg_trigger,pg_proc where tgfoid=pg_proc.oid and prolang=13; in your system to check if you have any :) > > > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > > > begin > > > If new.shipment_type_id = 4 then > > > --do something > > > end if; > > > return new; > > > end; > > > in the above example everything works if shipment_type_id is being > > > updated. If it is not I will receive a run-time error because > > > shipment_type_id is not part of the record new. > > All the fields of updated record must available in NEW variable. Refer > the manual for details. Forward your sql & run-time error for further > assistance. > > regards, > bhuvaneswaran > > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Timestamp
On Wed, 22 Oct 2003, Abdul Wahab Dahalan wrote: > Hi ! > In my database I've a field "departure" with timestamp without time zone > data type. > eg : > departure > 2003-11-01 14:29:46 Maybe SET DateStyle TO 'German' ; SELECT replace(departure,'.','-') from ; is close to what you want, but better leave it default and format the date (input/output) from your app. > > Now I want to select it as in this format 01-11-2003 14:29:46 How should > I do it?. > > Thank you for any help. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > Hi! > How do I list all the tables in the database which has a same column name?. SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 and t1.relname > Thanks > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] List table with same column name
On Thu, 23 Oct 2003, Peter Childs wrote: > > > On Thu, 23 Oct 2003 [EMAIL PROTECTED] wrote: > > > On Thu, 23 Oct 2003, Abdul Wahab Dahalan wrote: > > > > > Hi! > > > How do I list all the tables in the database which has a same column name?. > > > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and t1.relkind='r' > > and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' and a2.attnum>0 > > and t1.relname > That will not work. > > SELECT t1.relname,a1.attname,t2.relname from pg_class t1,pg_attribute > a1,pg_class t2,pg_attribute a2 where a1.attrelid=t1.oid and > t1.relkind='r'and a1.attnum>0 and a2.attrelid=t2.oid and t2.relkind='r' > and a2.attnum>0 and t1.relname a1.attisdropped=false and a2.attisdropped=false and t1.relname != > t2.relname; > > Why? > > Well two bugs. > 1> Dropped Columns needed for 7.3. > 2> Do you really need to know that column a in table 1 also appears in > table 1? Have you ever thought that x > Peter Childs > > > > > > > > > Thanks > > > > > > > > > ---(end of broadcast)--- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > -- > > -Achilleus > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
