| From: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Crosstab function |
| Date: | 2014-05-07 11:32:50 |
| Message-ID: | 536A19E2.70607@compulab.co.il |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
text="#000000">
<div class="moz-cite-prefix">What I have done in the past to build a
generic reporting application is to have the function write the
results you want in a table and return the tablename and then have
the client code call select * from that table. <br>
<br>
My standard report tablename is tblreport || userid;<br>
It gets dropped at the beginning of the function, so it is
basically a temp table that doesn't interfere with any other
users.<br>
<br>
Example:<br>
execute 'drop table if exists reports.tblreport' || v_userid ;<br>
execute 'drop sequence if exists reports.tblreport' ||
v_userid || '_id_seq; create sequence reports.tblreport' ||
v_userid || '_id_seq';<br>
v_sql=' create table reports.tblreport' || v_userid || ' as ';<br>
<br>
Sim<br>
<br>
On 05/06/2014 06:37 AM, Hengky Liwandouw wrote:<br>
</div>
<blockquote cite="mid:004601cf68dc$848e8420$8dab8c60$(at)com"
type="cite">
<pre wrap="">Very Clear instruction !
Thank you very much David. I will do it in my client app and follow your
guidance.
-----Original Message-----
From: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-general-owner(at)postgresql(dot)org">pgsql-general-owner(at)postgresql(dot)org</a>
[<a class="moz-txt-link-freetext" href="mailto:pgsql-general-owner(at)postgresql(dot)org">mailto:pgsql-general-owner(at)postgresql(dot)org</a>] On Behalf Of David G Johnston
Sent: Tuesday, May 06, 2014 11:01 AM
To: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a>
Subject: Re: [GENERAL] Crosstab function
Hengky Lie wrote
</pre>
<blockquote type="cite">
<pre wrap="">Hi David,
Are you sure that there is no pure sql solution for this ?
I think (with my very limited postgres knowledge), function can solve
this.
Which is the column header I need but I really have no idea how to use
this
as column header.
Anyway, If i can't do this in postgres, I will try to build sql string in
the client application (Windev) and send the fixed sql to the server
</pre>
</blockquote>
<pre wrap="">
Positive.
You could build the necessary string in a pl/pgsql language function but you
would still have trouble getting the data out of the function the way you
want; unless you output a single text column no matter the original data (
basically output a cvs version of the crosstab result).
There is no dynamic execution in SQL so even though you can get a string
that looks like what you want you cannot do anything with it. Only data is
allowed to be dynamic; the engine has to know the names and types of all
schema objects before it can start so there is no way a query can retrieve
these things from its own data. It's the whole cart-horse thing...
The solution is to build the query in the client and send it. Make sure you
look at the various "quote_" functions in order to minimize the risk of SQL
injection attacks. These are especially useful for pl/pgsql functions but
you might be able to use them in your first query so that you can avoid
coding all the quoting and escaping rules into your application. At minimum
double-quote all your identifiers and make sure there are no unescaped
embedded double-quotes. If the only variables are from data in tables
putting constraints on those tables would probably be useful as well - you
limit valid identifiers but minimized risk of bad data causing an issue.
David J.
--
View this message in context:
<a class="moz-txt-link-freetext" href="http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601">http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601</a>.
html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
</pre>
</blockquote>
<br>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 4.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David G Johnston | 2014-05-07 11:55:26 | Re: any psql \copy tricks for default-value columns without source data? |
| Previous Message | Serge Fonville | 2014-05-07 09:49:02 | Re: Oracle to PostgreSQL replication |