Re: How to Create a table in another database
Archana K N <[email protected]> Fri, 13 Nov 2015 11:05:38 +0530
| Newsgroups | gmane.comp.db.postgresql.php |
|---|---|
| Message-ID | <CACa6=i2cKRUXH9xcG00PREhu=bwG1tcr7-q-q3OsJN6=G2hyJQ@mail.gmail.com> |
--001a11c23e2ef8aaf00524656d75
Content-Type: text/plain; charset=UTF-8
Hello,
Here is my whole function...
*************************************************************************************************************
*CREATE OR REPLACE FUNCTION* retriev() *RETURN* void AS
$tmp$
DECLARE
colnam text[];
j text;
sum1 numeric;
totsum numeric;
datfrm text;
datto text;
*BEGIN*
datfrm := quote_literal('2000-11-16');
datto := quote_literal('2015-11-02');
totsum :=0;
colnam := array(select distinct table_schema::text from
information_schema.tables where table_schema like '%ab%');
* EXECUTE 'CREATE TABLE** db2.public.temp(totalsum numeric(100,20))';*
* FOR* j in array_lower(colnam,1).. array_upper(colnam,1)
*LOOP*
*EXECUTE ' SELECT* coalesce(sum(db1_col),0)date between
'||datfrm||' *AND* '||datto into sum1;
totsum := totsum + sum1;
* INSERT INTO db2.public.temp(totalsum) VALUES(totsum); *
*END LOOP;*
*END*
$tmp$
*LANGUAGE* 'plpgsql' *VOLATILE*;
--Calling the function
select retriev()
***************************************************************************************************************************************
Here the underlined queries are for *db2. *I need to create function in
the second database since first database contain data that is not for
public access. So from second database I call the *db1.retriev *and it uses
necessary data from *db1 *and result is saved as a table in second
database. Actual function has more parameters and result may contain many
rows , for that I want to create table in *db2*.
Regards
Archana
On Fri, Nov 13, 2015 at 1:51 AM, <[email protected]> wrote:
> On 12.11.2015 19:56, Guillaume Lelarge wrote:
>
> Hey,
>
> 2015-11-12 11:26 GMT+01:00 Archana K N <[email protected]>:
>
>> Hello,
>>
>> I created a postgres function in* db1*, from which I need to create
>> a table in another database say *db2. *I tried to use 'dblink' but it is
>> giving the error:-
>>
>>
>> ERROR: function dblink_connect(unknown, unknown) does not exist
>> LINE 1: select dblink_connect('dbname=mydb port=5432 host=localhost ...
>> ^
>> HINT: No function matches the given name and argument types. You
>> might need to add explicit type casts.
>>
>> Have been stuck on this for 2 days. Dont know what to do.
>>
>> I am using Windows 7 OS(32-bit) and due to certain security purpose
>> am using Postgres 9.0(cant update it to newer version).
>>
>> Please help me.
>>
>>
> Have you installed dblink on the db1 database? If yes, can you give us the
> complete query that begins with "select dblink_connect..." ?
>
>
> to me it looks like Archana is mixing PHP and SQL. "more details"
> certainly is the right reaction here.
>
> /Str.
>
--
-------------------
regards
archana
--------------------
--001a11c23e2ef8aaf00524656d75
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">=C2=A0 Hello,<div><br></div><div><br></div><div>=C2=A0 =C2=
=A0 =C2=A0 Here is my whole function...</div><div><br></div><div>**********=
***************************************************************************=
************************</div><div><div><font color=3D"#990000"><i>CREATE O=
R REPLACE FUNCTION</i> retriev() <i>RETURN</i> void =C2=A0AS</font></div><d=
iv><font color=3D"#990000">$tmp$</font></div><div><font color=3D"#990000"><=
br></font></div><div><font color=3D"#990000">DECLARE=C2=A0</font></div><div=
><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0colnam text[];</=
font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0j=
text;</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0sum1 numeric;</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0=
=C2=A0 =C2=A0 =C2=A0totsum numeric;</font></div><div><font color=3D"#99000=
0">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0datfrm text; =C2=A0</font></div><div><=
font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0datto =C2=A0text;<=
/font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0</font></div><div><i><font color=3D"#990000">BEGIN</font></i></div><div>=
<font color=3D"#990000"><br></font></div><div><font color=3D"#990000">=C2=
=A0 =C2=A0 datfrm :=3D quote_literal('2000-11-16');</font></div><di=
v><font color=3D"#990000">=C2=A0 =C2=A0 datto =C2=A0:=3D quote_literal('=
;2015-11-02');</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 t=
otsum :=3D0;</font></div><div><font color=3D"#990000">=C2=A0</font></div><d=
iv><font color=3D"#990000">=C2=A0 =C2=A0 colnam :=3D array(select distinct =
table_schema::text from information_schema.tables where table_schema like &=
#39;%ab%');</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0=C2=
=A0</font></div><div><font color=3D"#990000">=C2=A0=C2=A0</font></div><div>=
<font color=3D"#990000">=C2=A0 =C2=A0 <i>=C2=A0<u>EXECUTE 'CREATE TABLE=
</u></i><u> db2.public.temp(totalsum =C2=A0numeric(100,20))';</u></font=
></div><div><font color=3D"#990000"><br></font></div><div><font color=3D"#9=
90000">=C2=A0 =C2=A0 <i>=C2=A0FOR</i> =C2=A0j in array_lower(colnam,1).. ar=
ray_upper(colnam,1)</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 =
=C2=A0 <i>LOOP</i></font></div><div><font color=3D"#990000">=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 <i>EXECUTE ' SELECT</i> coalesce(sum(db1_col),0)date betw=
een '||datfrm||' <i>AND</i> '||datto into sum1;</font></div><di=
v><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0 totsum :=3D totsum + =
sum1;=C2=A0</font></div><div><font color=3D"#990000">=C2=A0 =C2=A0</font></=
div><div><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 =C2=A0<u><i> INSERT I=
NTO</i> db2.public.temp(totalsum) VALUES(totsum); =C2=A0 =C2=A0 </u>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0</font>=
</div><div><font color=3D"#990000">=C2=A0 =C2=A0 =C2=A0 <i>END LOOP;</i></f=
ont></div><div><font color=3D"#990000"><br></font></div><div><i><font color=
=3D"#990000">END</font></i></div><div><font color=3D"#990000">$tmp$</font><=
/div><div><font color=3D"#990000"><i>LANGUAGE</i> 'plpgsql' <i>VOLA=
TILE</i>;</font></div><div><font color=3D"#990000"><br></font></div><div><f=
ont color=3D"#990000"><br></font></div><div><font color=3D"#990000">--Calli=
ng the function</font></div><div><font color=3D"#990000"><br></font></div><=
div><font color=3D"#990000"><br></font></div><div><font color=3D"#990000">s=
elect retriev()</font></div></div><div><br></div><div>*********************=
***************************************************************************=
***************************************</div><div><br></div><div><font colo=
r=3D"#000000">=C2=A0Here the underlined queries are for <i style=3D"font-we=
ight:bold">db2. =C2=A0</i>I need to create function in the second database =
since first database contain data that is not for public access. So from se=
cond database I call the <b style=3D"font-style:italic">db1.retriev </b>and=
it uses necessary data from <b style=3D"font-style:italic">db1 </b>and res=
ult is saved as a table in second database. Actual function has more parame=
ters and result may contain many rows , for that I want to create table in =
<b><i>db2</i></b>.</font></div><div><font color=3D"#000000"><br></font></di=
v><div><font color=3D"#000000"><br></font></div><div><font color=3D"#000000=
">Regards</font></div><div><font color=3D"#000000">Archana=C2=A0</font></di=
v></div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">On Fri, N=
ov 13, 2015 at 1:51 AM, <span dir=3D"ltr"><<a href=3D"mailto:itb348@gma=
il.com" target=3D"_blank">[email protected]</a>></span> wrote:<br><blockq=
uote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc =
solid;padding-left:1ex">
=20
=20
=20
<div bgcolor=3D"#FFFFFF" text=3D"#000000"><span class=3D"">
<div>On 12.11.2015 19:56, Guillaume Lelarge
wrote:<br>
</div>
<blockquote type=3D"cite">
=20
<div dir=3D"ltr">Hey,<br>
<div class=3D"gmail_extra"><br>
<div class=3D"gmail_quote">2015-11-12 11:26 GMT+01:00 Archana K
N <span dir=3D"ltr"><<a href=3D"mailto:[email protected]=
" target=3D"_blank">[email protected]</a>></span>:<br>
<blockquote class=3D"gmail_quote">
<div dir=3D"ltr">Hello,
<div><br>
</div>
<div>=C2=A0 =C2=A0 =C2=A0I created a postgres function in<i=
><b> db1</b></i>,
from which I need to create a table in another
database say <b>db2. </b>I tried to use 'dblink' =
but
it is giving the error:-</div>
<div><br>
</div>
<div><br>
</div>
<div>=C2=A0 =C2=A0ERROR: =C2=A0function dblink_connect(unkn=
own,
unknown) does not exist
<div>=C2=A0 =C2=A0LINE 1: select dblink_connect('dbna=
me=3Dmydb
port=3D5432 host=3Dlocalhost ...</div>
<div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0^</div>
<div>=C2=A0 =C2=A0HINT: =C2=A0No function matches the giv=
en name and
argument types. You might need =C2=A0 =C2=A0 =C2=A0 to =
add explicit
type casts.</div>
<div><br>
</div>
<div>=C2=A0 =C2=A0 =C2=A0Have been stuck on this for 2 da=
ys. Dont
know what to do.=C2=A0</div>
<div>=C2=A0 =C2=A0 =C2=A0</div>
<div>=C2=A0 =C2=A0 =C2=A0I am using Windows 7 OS(32-bit) =
and due to
certain security purpose am using =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 Postgres
9.0(cant update it to newer version).=C2=A0</div>
<div><br>
</div>
<div>=C2=A0 =C2=A0 =C2=A0 Please help me.=C2=A0</div>
<span>
<div><br>
</div>
</span></div>
</div>
</blockquote>
<div><br>
</div>
<div>Have you installed dblink on the db1 database? If yes,
can you give us the complete query that begins with
"select dblink_connect..." ?<br>
<br>
</div>
<br>
</div>
</div>
</div>
</blockquote></span>
to me it looks like Archana is mixing PHP and SQL.=C2=A0 "more det=
ails"
certainly is the right reaction here.<br>
<br>
/Str.<br>
</div>
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><div class=
=3D"gmail_signature">-------------------<br>regards<br>archana<br>---------=
-----------</div>
</div>
--001a11c23e2ef8aaf00524656d75--