;"Jamie Tufnell"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi codeWarrior,
>
> codeWarrior wrote:
>> > For user_ids that have more than 50 rows, I want to keep the most
>> > recent 50 and delete the rest.
>> How
How about using a trigger to call a stored procedure ? [ON INSERT to
user_item_history DO ...]
and have your stored procedure count the records for that user and delete
the oldest record if necessary...
IF (SELECT COUNT(*) WHERE user_id = NEW.user_id) >= 50 THEN
-- DELETE THE OLDES
Fisrt -- you probably want to start by doing fully qualified JOINS and then
you want to allow joins with nulls on the columns that are allowed to be
empty: I am doing this sort of off the top of my head ... but the thing you
need to do generally is to COMPLETELY QUALIFY all of your joins and the
ED]> wrote in message
news:[EMAIL PROTECTED]
> On Fri, Jan 19, 2007 at 07:45:40PM -0800, codeWarrior wrote:
>> AFAIK: You cannot have multiple primary keys. How would you know which
>> one
>> is the actual key ?
>
> You can have a multi-column primary key, though
--
AFAIK: You cannot have multiple primary keys. How would you know which one
is the actual key ?
FYI: What you are really talking about are table contraints... When you have
multiple unique column constraints -- they are generally referred to as
"table constraints" not multiple primary keys
You mean like this:
CREATE OR REPLACE VIEW sys_tabledef AS
SELECT columns.table_catalog, columns.table_schema, columns.table_name,
columns.column_name, columns.ordinal_position, columns.column_default,
columns.is_nullable, columns.data_type, columns.character_maximum_length,
columns.character
I dont think you need the double-left join
SELECT * FROM STORIES ST
LEFT JOIN TAGS TG ON TG.tagkey = ST.storykey
WHERE TG.tag = "science"
"MRKisThatKid" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi, i've posted this in various places but I'm really struggling to
> find
17.10.2. Locale
and Formatting
DateStyle (string)
Sets the display format for date and time values, as well as the rules for
interpreting ambiguous date input values. For historical reasons, this
variable contains two independent components: the output format specification
Can you show us the code for your SP ? I'd like to
see what the RETURNS statement is in the sp declaration (CREATE OR REPLACE
PROCEDURE sproc(type, type, type) RETURNS SETOF returntype AS ...)
You might reconsider your SELECT * FROM sproc() AS
() -- SELECT * retrieves ALL columns defined
I would hope that your choice to use postgreSQL is because it is superior
technology that scales well financially... not because you get a warm fuzzy
from all your friends on the mailing lists...
"Oisin Glynn" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Richard Broersma Jr wr
Personally: I think your posts are getting annoying. This isn't SQLCentral.
Learn to write your own damn queries or even better - buy a book on SQL...
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> hi all, i posted this problem on the novice thread,
> but it makes much more sense
I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my
PKEY's -- as an absolute rule -- I guess I am a purist... Everything else
(the other columns) can have unique constraints, etcetera and be FOREIGN
KEYS, etc...
Try INSERTING your 100 character "natural" key into a table wi
Keith: This is the general approach I use over and over and over -- This is
a PLPGSQL function that returns a SETOF
tablename%ROWTYPE
If you need the full schema and table and everything that goes with this --
let me know ---
CREATE OR REPLACE FUNCTION sys_aclsubmenu(int4) RETURNS SETOF
sys
In your function why not create a temporary table then use that for your
processing ?
CREATE TEMPRORARY TABLE tabledest AS (SELECT * FROM tblsrc WHERE condition);
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I user PostgreSQl 8.0.4 on Win2003 Server and write a functio
Talk about obfuscated Are you trying to retrieve the table structure /
schema from the PG System Catalogs ?
If so -- you are better off using a VIEW instead of a manual procedure
because it will automatically kepp up with the current schema definition...
Try this:
-- DROP VIEW sys_table_sc
SELECT fieldlist, CASE WHEN myboolean = true THEN 1 ELSE 0 END
FROM tblname WHERE condition;
""Jesper K. Pedersen"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> I am have some difficulties converting the Microsoft field of type
> YESNO (which is a simple boolean true/false)
WHERE event_type < NOW() - interval '30 days';
"Brian Doyle" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I have a table like:
>
> events
> visitor_uid varchar(32)
> event_type varchar(32)
> event_type timestamp
>
> I would like to select events from the table that are olde
Sounds like a mis-guided approach to me...
You might want to consider using latitude and longitude or zip codes or
taking more of a traditional "GIS" approach rather than duplicating data
across redundant databases.
Another issue is that you end up having to query every database to find
proxim
I think its cause you changed your procedure from being written in SQL to
being writtern in PLPGSQL in your second implementation
Sets of records are returned from a PLPGSQL function with a RETURN statement
... not a SELECT...
Check out the sections of the manual that talk about PLPGSQL...
You're looking for a technique generally referred to as a "pivot table"
which is really a non-normalized or aggregate view of relational data
You'll find plenty of examples if you Google "pivot table".
"Paul" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> I'm looking
The SQL data type "money" in postgreSQL was deprecated several versions
ago... however -- it is still available in the system. The definitions is:
CREATE TYPE money
(INPUT=cash_in, OUTPUT=cash_out, DEFAULT='',
INTERNALLENGTH=4, ALIGNMENT=int4, STORAGE=PLAIN);
ALTER TYPE money OWNER TO p
The following trigger procedure works for me you'd need to adjust this
to manipulate YOUR table schema:
DROP FUNCTION dmc_comp_plan_duplicates() CASCADE;
CREATE OR REPLACE FUNCTION dmc_comp_plan_duplicates() RETURNS "trigger" AS
$BODY$
DECLARE did integer;
BEGIN
SELECT COALESCE(id,
I dont consider this to be a design problem... In fact... I do the inherited
table thing pretty routinely... It (table inheritance) works very well if
you need to implement a Sarbanes-Oxley audit trail or a "virtual" rollback
system without the overhead of managing transactions.
Consider the re
"Tom Lane" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> solarsail <[EMAIL PROTECTED]> writes:
>> I have a large number of tables with a common naming convention
>
>> mytable001, mytable002, mytable003 ... mytable00n
>
>> I would like to do a query across all of the tables, howeve
"Mario Splivalo" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I've learned that one can't use temporary tables within the function
> unless
> EXECUTE'd the SELECTS from that temp table.
>
> So, I have a function like this:
>
> CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType
"Ferindo Middleton Jr" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Is there some reason why the SERIAL data type doesn't automatically have a
> UNIQUE CONSTRAINT. It seems that the main reason for using it is so that
> the value for this field keeps changing automatically and
You are looking for what is commonly referred to as a "pivot-table" or a
"cross-tab". Pivot-tables are a fairly sophisticated, non-normalized view of
a dataset, nd most commonly appear in spreadsheets and are used for
financial or statistical analysis.
These queries typically use an aggregate f
SELECT 1032::numeric(10,2);
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi.
> I have searched in mailing-list archives about converting types, but I
> couldn't
> found a function or clause that convert a numeric type to money type.
> How Can I convert this types?
>
> => selec
You're doing update right ??? Just update the column... It's even easier if,
when you do your updates... You just:
UPDATE blah SET field = value,, updatestamp = 'Now()' WHERE
condition...
GP
"Neal Lindsay" <[EMAIL PROTECTED]> wrote in message
b1r864$2mpp$[EMAIL PROTECTED]">news:b1r864$2mpp
No.
Try instead
CREATE DATABASE newname FROM TEMPLATE oldname;
DROP DATABASE oldname;
""Wei Weng"" <[EMAIL PROTECTED]> wrote in message
000c01c2c32b$09526500$5301a8c0@monet">news:000c01c2c32b$09526500$5301a8c0@monet...
> This is a multi-part message in MIME format.
>
> --=_NextPart_000_0009
30 matches
Mail list logo
