pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: raptor <raptor@unacs.bg>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Long story !!! Please HELP !!!
Date: Tue, 14 Sep 1999 14:45:36 -0400
Message-ID: <37DE97D0.218FA124@unacs.bg> (raw)

hi there,

I'm new to the list AND have one big problem.(pls. don't care for some
syntax errors, I'm writing this on the fly).
Let me explain the thing from the begining. Before a 2-3 days I began
making a REPORTS (for one project I'm doing).
I decided to use Postgres 6.4.2 (RH6) 'cause it is free and support
SubSelsects & Views ?!?! (it will be good to support OUTER JOINS but
..:")).

So, I have the folowing tables created as VIEWS :

View1
id | amount |
---------
2  | 1000     |
5  |  300      |
__________

View2
id | hit     |
---------
1  |  1        |
2  |  3        |
5  |  1        |
__________

They are created like this (pls. read to the end :"))
<conditions> contains subselect and other stuff:

CREATE VIEW View1 AS
 SELECT X.id, SUM(Y.amount) ...
FROM X, Y
WHERE <conditoins>

CREATE VIEW View2 AS
 SELECT X.id, COUNT(Y.hit) ...
FROM X, Y
WHERE <conditoins>

Then I decided to combine the results (I used UNION to simulate OUTER
JOIN, but at the moment I will stop only on the first select of the
JOIN). OK here is it :

SELECT v1.id, v1.amount, v2.hit
FROM view1 AS v1, view2 AS v2
WHERE v1.id = v2.id

UNION

....second select ...

What was my surprise that the result was :

Result
id  | amount | hit |
--------------
2   |   3000   |   6   |
5   |   900     |   3   |
_______________

??!?!!?.OK I made a search in mail archives and saw that the views has
some problems !?. Then dloaded the new .rpm Postgres 6.5.x and installed
it (I thought the problem is resolved, but ... :"(, NO AS I READ in THE
NEW DOCS USING AGGREGATES IN VIEWS IS BROKEN DOESN"T WORK).
Even if I make the views in this way :

CREATE VIEW View1 AS
 SELECT X.id, Y.amount
FROM X, Y
WHERE <conditoins>

CREATE VIEW View2 AS
 SELECT X.id, Y.hit
FROM X, Y
WHERE <conditoins>

and then :

SELECT v1.id, SUM(v1.amount), SUM(v2.hit)
FROM view1 AS v1, view2 AS v2
WHERE v1.id = v2.id

It again doesn't work. So I read at the new docs again that there is a
way to do this as FUNCTION (no comments how :"(). So I made something
like this (it works, but ... ):

CREATE FUNCTION View1Sum(oid) RETURN float8 AS
' SELECT Sum(View1.amount) FROM View1 WHERE id = $1;'
LANGUAGE 'sql'

CREATE FUNCTION View2Sum(oid) RETURN float8 AS
' SELECT Sum(View2.hit) FROM View2 WHERE id = $1;'
LANGUAGE 'sql'

I don't how can pass the names of the fields or tables as parameters (if
this is possible at all ?!?), like this :


CREATE FUNCTION ViewSum(text, text, text) RETURN float8 AS
' SELECT Sum($2.$1) FROM $2 WHERE $3;'
LANGUAGE 'sql'

($1 - field name, $2 - table name, $3 - condition as text)
:"(::::::::::::::::
NOPE.
Why I want this ? 'cause the views are created on the fly by Perl
script,so if I use this first method, I must create also the functions
on the fly, BAD. (Every view has a unique name)
Another reason 'cause "functions way" is bad solution is 'cause If I
have 1000 rows result, for every id I must execute similar function ~6
times (my situation is more complicated than this I'm describing here)
i.e. 6000 SELECT for doing agregates ?!?!:"(

PLEASE TELL ME IS THERE BETTER SOLUTION TO THIS !!! PLEASE.

I'm in the middle of the project and don't know what to do, it is a bad
time to change the DB. Is there any other free DB that "REALLY" supports
SubSelects, Views and OUTER JOINS.


PS. If I use SELECT INTO instead CREATE VIEW all is working fine, but
this isn't the solution, 'cause it is better to use text file instead DB
:") - it will be faster I think :")

Thax alot in advance.
=====
iVAN
raptor@unacs.bg
=====




Message-ID: <37DE97D0.218FA124@unacs.bg>
Permalink:  ../37DE97D0.218FA124@unacs.bg/
Also on:    postgresql.org/message-id/37DE97D0.218FA124@unacs.bg

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: raptor@unacs.bg
  Subject: Re: Long story !!! Please HELP !!!
  In-Reply-To: <37DE97D0.218FA124@unacs.bg>
  References: <37DE97D0.218FA124@unacs.bg>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox