[SQL] passing parameters between forms

[Sharmad Naik]

Hi,
I have created a database with following structure

CREATE TABLE member(
mem_id INT,
name TEXT,
age INT);

the first page is as follows :



This is the  first page 

   Member ID :  
   
   Member NAME :
   
   Member AGE :   
   
 
   
  



The second page that is search 2.php is as follows :




This is the Displaying page
");
echo("$mem_id");
echo("$name");
echo("$age");
echo("");
//  require("search3.php");
//  series($mem_id,$name,$age);
echo("");
}
else{ echo("Inconvient data");}
?>



This is search3.php



In my search3.php I want to insert (which i have not given) or atleast echo the 
parameter like mem_id, age and name.My Q
+is how do i pass the parameters between search2.php and search3.php.If there is 
+another way out pls tell.
I tried using functions to get the work done.but couldn't get my way out.

Can anybody help me?


Sharmad

-- 
The secret of the universe is @*&í!'ñ^#+ NO CARRIER
___  _  _  _
|_|_||_||_||\/||_|| \
_|| || || \|  || ||_/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Trigger Function and Html Output

[Richard Huxton]

From: "Stef Telford" <[EMAIL PROTECTED]>

> Richard Huxton wrote:
>
> Each select works on a view, rather than hardcode the view into the
> perl CGI, i would rather have the table header/column titles returned
> as the first item as text/html (i know about the func procedure to get the
> table_attributes) and then all the formatting thereafter done by the
database
> for each row.
>
> maybe i am naive in thinking this way, but surely the a database function
> formatting the returned string must be quicker then perl. (speaking
> generically of course, i conceed that there are times when the reverse
> is true)

Not sure you'd notice much of a speed difference - in most cases other
overheads will be the deciding factor. Of course, YMMV.

> thank you for the input, and if i was jst starting out i would agree with
> you. I cant really explain it any better than i have previously, but
> hopefully you will see that i want to use the database to do this.
>
> hopefully that isnt that strange a request ;)

Strange or not, it's your database and you're the only one in a position to
make this sort of decision.

I did think that it was the psql front-end that did the HTML stuff. But -
there seems to be html stuff in fe-print.c (src/interfaces/libpq) and the
Perl module Pg has html output options in $result->print(...) which might be
flexible enough for your needs.

I must admit I've never used it, so I don't know if you can add attributes
to table elements, use styles etc.

HTH

- Richard Huxton


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Calling functions inside a function: behavior

[Edipo Elder Fernandes de Melo]

Hi all,
 

 
I wrote a function that call another functions, like this:
 

 
FUNCTION (...) AS '(...)BEGIN
 
PERFORM FUNCTION1();
 
PERFORM FUNCTION2();
 
(...)
 
END;' LANGUAGE 'PLPGSQL';
 

 
The problem is that takes a long time to execute (I stoped after three 
days executing). To see who taking more time to execute, I modified each one 
to return the time of its execution. Then, a made a scrip like:
 

 
SELECT TIMESTAMP 'NOW';
 
SELECT FUNCTION1();
 
SELECT FUNCTION2(); (...)
 
SELECT TIMESTAMP 'NOW';
 

 
and, for my surprise, it take minutes to run. I read the documentation 
and I didn't found any coment abou this behavior. Can anyone explain this?
 

 
Thank you,
 

 
Edipo Elder
 
[[EMAIL PROTECTED]]
 

_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] passing parameters between forms

[Richard Huxton]

From: "Sharmad Naik" <[EMAIL PROTECTED]>

> Hi,
> I have created a database with following structure
>
> CREATE TABLE member(
> mem_id INT,
> name TEXT,
> age INT);
>
> the first page is as follows :
> 
>
>Member ID :
>

> The second page that is search 2.php is as follows :
>
> 
> echo("");
> echo("$mem_id");

Create a hidden input element that will be posted on to the third form:

echo('');

> echo("$name");
> echo("$age");
> echo("");
> //  require("search3.php");
> //  series($mem_id,$name,$age);
> echo("");

> In my search3.php I want to insert (which i have not given) or atleast
echo the parameter like mem_id, age and name.My Q
> +is how do i pass the parameters between search2.php and search3.php.If
there is another way out pls tell.
> I tried using functions to get the work done.but couldn't get my way out.

This is more of a cgi question really - I'd suggest a quick visit to
www.faqs.org and have a look at the CGI faq.

- Richard Huxton


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: Calling Java from psql (was Re: [SQL] requesting help)

[Peter T Mount]

Quoting A James Lewis <[EMAIL PROTECTED]>:

> 
> Before I go investigating this, is it possible to trigger an arbitrary
> program from the SQL, say a shell script?

In theory yes, but I'd suspect not a good idea.

Even more so with java, as the JVM's startup time is pretty big. Having it talk 
to an already running JVM is the best option.

> Also, why am I getting "not subscribed messages", I am subscribed since
> I'm replying to a message that was sent to me!!

I'm getting that with the patches list as well.

Peter

> On Thu, 29 Mar 2001, Peter Mount wrote:
> 
> > At 21:37 26/03/01 +0200, Mathijs Brands wrote:
> > >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly
> wrote:
> > > > Mathijs Brands writes:
> > > >
> > > > > Has anybody ever tried calling Java code from a pgsql trigger
> written
> > > > > in C? Shouldn't this be possible using JNI?
> > > >
> > > > I have, and given the current Java implementations it's a
> desaster.
> > >
> > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling
> to
> > >work stable either :(
> >
> > Not having looked at the PHP-Java link, are they working as one
> process or
> > is it some IPC type link?
> >
> > PeterM
> >
> >
> > ---(end of
> broadcast)---
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to [EMAIL PROTECTED] so that your
> > message can get through to the mailing list cleanly
> >
> 
> A. James Lewis ([EMAIL PROTECTED])
> If your OS needs a virus detector... RUN!!!
> ...Out and get Linux!
> 
> 



-- 
Peter Mount [EMAIL PROTECTED]
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] Error:TypeCreate: type links already defined

[Najm Hashmi]

Hi all, I have posted my question since last Thurday and noone has answered it
yet. My problems is that I droped a table  using  pgaccess from pg_class. Now
If  I try to create that table I get the following error: TypeCreate: type
links already defined.
Could some one help  me out here.
Regards


begin:vcard 
n:Hashmi;Najm
x-mozilla-html:FALSE
org:Mondo-Live.com;www.flipr.com
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
x-mozilla-cpt:;6144
fn:Najm Hashmi
end:vcard



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Calling functions inside a function: behavior

[Roberto Mello]

On Mon, Apr 02, 2001 at 08:50:32AM -0300, Edipo Elder Fernandes de Melo wrote:
>  
> and, for my surprise, it take minutes to run. I read the documentation 
> and I didn't found any coment abou this behavior. Can anyone explain this?

Cool, one more Brazilian in the list :)

I don't see any difference between calling the functions with a select
or a perform, except that with perform the return value is discarded.
What version of PG are you running and what are those functions
written in (PL/pgSQL, PL/Tcl, PL/Perl)?
Can you post a snippet of the functions here?

-Roberto
-- 
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
   http://www.sdl.usu.edu - Space Dynamics Lab, Developer
TOFU -> Text Oben Fullquote Unten

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Error:TypeCreate: type links already defined

[Richard Huxton]

From: "Najm Hashmi" <[EMAIL PROTECTED]>

> Hi all, I have posted my question since last Thurday and noone has
answered it
> yet. My problems is that I droped a table  using  pgaccess from pg_class.
Now
> If  I try to create that table I get the following error: TypeCreate: type
> links already defined.
> Could some one help  me out here.
> Regards

Need more information Najm - obivously PG is trying to create a type "links"
and failing - do you have anything called "links" in your table definition
or the rest of your database?

Perhaps look in the pg_types table:

select oid,* from pg_types where typname like 'lin%';

Don't go deleting it without knowing what it is though. If your table is
called "links" I'd guess it's fine to delete it, but I'd take a backup first
anyway.

- Richard Huxton


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Fwd: [SQL] Calling functions inside a function: behavior

[Josh Berkus]

Edipo,
 
> FUNCTION (...) AS '(...)BEGIN 
> PERFORM FUNCTION1(); 
> PERFORM FUNCTION2(); 
> (...) 
> END;' LANGUAGE 'PLPGSQL'; 

I'm not sure about that syntax (PERFORM).  I ususally set my functions
equal to a value ('remote_result := Function1(paramater)').  This has
the added advantage of letting me use an exit value from my sensted
function to communicate with the parent function ('IF remote_result =
'ERROR' THEN ... ').  I'm just not sure what bugaboos you may be running
into with PERFORM.

One possibility (Tom, Jan, verify me on this):  All calls in a function
are automatically nested in a transaction.  Thus, if FunctionM calls
Function1, 2, and 3, then the system should reverse Functions 1, 2, and
3 if M errors out at any point.  This means that all of the changes made
by the nested calls need to be cached somehow; on a slow or low-memory
system, this could lead to bogging down as your machine utilizes its
swap space if Functions 1, 2, and 3 involve heavy data interactions.

One way to test this, is to modify your test script as follows:

BEGIN WORK;
PERFORM Function1;
SELECT current_timestamp;
PERFORM Function2;
SELECT current_timestamp;
etc ...
COMMIT WORK;

If the test script bogs down as well, you have your answer although the
workaround may be tricky to implement.

-Josh Berkus




__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

[SQL] very very slow .....

[J.Fernando Moyano]

I have a system called "eisenor" running in 6.5 and it runs very well.
I'have installed 7.0.2 at home and i'm working with it and "eisenor".

Tables are created without problems, and functions too (except some minor 
changes related to date/time).

Very complex queries execute without error ... but some of them are really 
SLOOOooOOOW !!

The execution time of certain queries is 20 or 30 times longer in 7.0.2 that 
6.5. (20 or 30 seconds where before it was 1 or 2 seconds !!!)

The table structure is the same, with same indexes, constrains,etc... ¿what 
happens??? ¿ideas??? iI it a 7.0.2 "feature"? Must i try 7.2 ?

Thanks !!

-- 
Fernando Moyano

Frase del día:
--
Real programmers do: copy con program.exe

(*) SymeX ==> http://symex.lantik.com
(*) WDBIL ==> http://wdbil.sourceforge.net
(*) Informate sobre LINUX en http://www.linux.org

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: Fwd: [SQL] Calling functions inside a function: behavior

[edipoelder]

Em 02 Apr 2001, Josh Berkus escreveu: 

>BEGIN WORK; 
>COMMIT WORK; 

In time... I think that could be great if postgresql implement a commit 
inside functions. 

Abracos, 

Edipo Elder 
[[EMAIL PROTECTED]] 

_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] using for rec inside a function: behavior very slow

[Jie Liang]

I have a function:
CREATE FUNCTION hasdup(text) RETURNS int4 AS '
declare
   v_id int4;
   rat1 text;
   rat2 text;
   v_urltext;
   rec  record;

begin
 select id into v_id from urlinfo where url = $1;
 if NOT FOUND then
return -1;
 end if;
 select codestr(v_id) into rat1;
 v_url:= $1||''%'';
 for rec in select id,url from urlinfo where url like v_url order by
url loop
raise notice ''%'',rec.url;
   select codestr(rec.id) into rat2;
   if rec.id <> v_id and rat1 = rat2 then
  update urlinfo set list = 1 where id = rec.id;
  return rec.id; 
   end if;
 end loop; 
 return 0;
end; 
' LANGUAGE 'plpgsql';


'where url like clause' is very slow in inside the function,
but when I directly use this statement in SQL, it is very quick,
is any quick way to return match:
where field like 'something%' inside the plsql function??


Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] select statement inside a function: behavior bad

[Jie Liang]

I tested select statement inside sql and plpgsql function,
very slow


CREATE FUNCTION geturllike(text) RETURNS SETOF text AS '
SELECT url as url FROM urlinfo WHERE url LIKE $1;
'LANGUAGE 'sql';

CREATE FUNCTION hasdup(text) RETURNS int4 AS '
declare
   v_id int4;
   rat1 text;
   rat2 text;
   v_urltext;
   rec  record;

begin
 v_url:= $1||''%'';

 for rec in select id,url from urlinfo where url like v_url order by
url loop
raise notice ''%'',rec.url;
 end loop;
 return 0;
end;
' LANGUAGE 'plpgsql';

Why so slow
Is it a bug??


Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] using for rec inside a function: behavior very slow

[Tom Lane]

Jie Liang <[EMAIL PROTECTED]> writes:
>  v_url:= $1||''%'';
>  for rec in select id,url from urlinfo where url like v_url order by
> url loop

[ is slow ]

LIKE index optimization doesn't happen if the LIKE pattern is a variable
when the plan is created.

In 7.1 you can work around this problem by using plpgsql's FOR ... EXECUTE
notation, but I don't think there's any good answer in 7.0.

for rec in execute ''select id,url from urlinfo where url like 
''||quote_literal(v_url)||'' order by url'' loop

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] very very slow .....

[Tom Lane]

"J.Fernando Moyano" <[EMAIL PROTECTED]> writes:
> The execution time of certain queries is 20 or 30 times longer in 7.0.2 that 
> 6.5. (20 or 30 seconds where before it was 1 or 2 seconds !!!)

Have you done a VACUUM ANALYZE?

Other than that, there's no generic advice.  You'll need to provide some
details about the problem queries and the plans you get for them in 6.5
and 7.0.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] Appropriate indices to create for these queries

[Gerald Gutierrez]

I've been looking into indices and which ones to create and I'm getting
myself a little confused. The "PostgreSQL Introduction and Concepts" book
didn't help very much. I wonder if a kind soul can give me some tips.

SELECT * FROM T1 WHERE a=1 and b='hello';

Is the appropriate index for this query:

CREATE INDEX ndx ON T1 (a, b) ?

When I was testing, it seemed that even if I created the index with only
"a", EXPLAIN told me that it would just do an index scan, seemingly
indicating that it didn't matter whether I had an "a" index, or an "a, b"
index.

How about for WHERE a=1 or b='hello' and other more complex forms? Is there
documentation that describes a variety of different queries and what kind of
indices are best?

Thanks.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] passing null parameter to plpgsq functions

[Picard, Cyril]

Hello 
I wrote a plpgsql function with few parameters ; when I call the function,
some of the parameters can be null.
In this case, all the parameters are considered as null in the function's
body ! is it a feature ? how can I work around this ?

Example :
CREATE FUNCTION foo(text, text) RETURNS bool  AS '
begin
IF ($1 IS NULL) AND ($2 IS NULL) THEN 
RETURN FALSE;
ELSE 
RETURN TRUE;
END IF;
end;
' LANGUAGE 'plpgsql';


SELECT foo(null,'bouh') => false (I expected true)
SELECT foo('schfk',null) => false (I expected true)
SELECT foo(null,null) => false (OK)
SELECT foo('schfk','bouh') => true (OK)




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly