headers
Collin Peters | 1 May 2007 01:58
Picon

Dynamic prepare possible in plpgsql?

Is it possible to have a dynamic PREPARE statement in plpgsql?

Something like

	PREPARE users_plan ( || 'text, text' || ) AS
	INSERT INTO pp_users( || 'col1, col2' || )
	VALUES($1, $2);

Regards,
Collin

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Permalink | Reply | 5 comments |
headers
Collin Peters | 1 May 2007 02:03
Picon

"is not distinct from" syntax error?

From: http://www.postgresql.org/docs/8.2/static/functions-comparison.html

" The ordinary comparison operators yield null (signifying "unknown")
when either input is null. Another way to do comparisons is with the
IS [ NOT ] DISTINCT FROM construct:

expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression

For non-null inputs, IS DISTINCT FROM is the same as the <> operator.
However, when both inputs are null it will return false, and when just
one input is null it will return true. Similarly, IS NOT DISTINCT FROM
is identical to = for non-null inputs, but it returns true when both
inputs are null, and false when only one input is null. Thus, these
constructs effectively act as though null were a normal data value,
rather than "unknown"."

However, when I do:
SELECT	1 IS NOT DISTINCT FROM 2

I get "ERROR: syntax error at or near "DISTINCT""

I can do:
SELECT	NOT (1 IS DISTINCT FROM 2)

What is the problem here?

Regards,
Collin
(Continue reading)

Permalink | Reply | 1 comment |
headers
Tom Lane | 1 May 2007 02:46
Picon

Re: "is not distinct from" syntax error?

"Collin Peters" <cadiolis <at> gmail.com> writes:
> However, when I do:
> SELECT	1 IS NOT DISTINCT FROM 2
> I get "ERROR: syntax error at or near "DISTINCT""

> What is the problem here?

Using a pre-8.2 server, likely.

			regards, tom lane

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

               http://www.postgresql.org/docs/faq
Permalink | Reply | 0 comments |
headers
Tom Lane | 1 May 2007 02:52
Picon

Re: Dynamic prepare possible in plpgsql?

"Collin Peters" <cadiolis <at> gmail.com> writes:
> Is it possible to have a dynamic PREPARE statement in plpgsql?

Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE
commands, but it seems awfully brute-force.  What do you really need
to accomplish here?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Permalink | Reply | 4 comments |
headers
Collin Peters | 1 May 2007 18:52
Picon

Re: Dynamic prepare possible in plpgsql?

I have a plpgsql function which is doing a loop over one table of user
data and then inserting that data in various tables.  Example:

loop over user table (temp data)
   insert into users1 table
   insert into users2 table
   etc....
end loop

Is it faster to use PREPARE for the various INSERT statements inside a
plpgsql function?  Perhaps I am wrong and it does its PREPARE work
when the function is parsed.

Collin

On 4/30/07, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> "Collin Peters" <cadiolis <at> gmail.com> writes:
> > Is it possible to have a dynamic PREPARE statement in plpgsql?
>
> Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE
> commands, but it seems awfully brute-force.  What do you really need
> to accomplish here?
>
>                         regards, tom lane
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
(Continue reading)

Permalink | Reply | 3 comments |
headers
Jonah H. Harris | 1 May 2007 19:16
Picon
Gravatar

Re: Dynamic prepare possible in plpgsql?

On 5/1/07, Collin Peters <cadiolis <at> gmail.com> wrote:
> Is it faster to use PREPARE for the various INSERT statements inside a
> plpgsql function?  Perhaps I am wrong and it does its PREPARE work
> when the function is parsed.

IIRC, PLpgSQL automagically prepares each statement behind the scenes
on the first use.

--

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris <at> enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Permalink | Reply | 2 comments |
headers
Dmitry Turin | 2 May 2007 07:05
Picon

Re: We all are looped on Internet: request + transport = invariant

Good day, Joe.

Joe and other respected comrades,
don't be angry, when my letter will be late.
This is caused by technical schedule of our internet.

J> ... and C, PHP, Python, etc., to create an application to input
J> and maintain the data.

Question remains open:
for what people must learn php, if they need mainly
"table1.table2 >>;" or "create view x ...; x>>;"

I explain this thought.
For example, TML consist of two parts: SIMPLE and DIFFICULT.
Border between them is so: users will use mainly
"<tagname> >>;" or "tablename >>;"

Not all of TML will be used by low-skill users.
But this don't canceals, that users need simple part of TML.

J> Do you think
J> if Amazon gave raw read access to their underlying database,
J> many people would jump to query it with SQL, TML or any computer language?

I speak about situation,
when people need to create complex "database-visualizer"
(visualizer is a browser, spreadsheet, etc).
Practice show, that they use only database in this case
(by sql-terminal like "pgsql.exe").
(Continue reading)

Permalink | Reply | 1 comment |
headers
Joe | 2 May 2007 14:47

Re: We all are looped on Internet: request + transport = invariant

Hi Dmitry,

On Wed, 2007-05-02 at 08:05 +0300, Dmitry Turin wrote:
> J> The average man or woman on the street
> 
> For what you say about street ?
> Average people, which you can meet on street, make physical job.

That is an American colloquialism to refer to just about anyone,
regardless of what kind of work they do.  The point is that --using the
Pareto principle-- 80% (probably much more) of the people don't know SQL
or are fluent in other programming languages, and they don't want to be
bothered with *any* of it except to use the products and services that
are made possible through them.

According to the U.S. Bureau of Labor Statistics (BLS) there were
455,000 programming jobs in 2004.  Even if you raise that by an order of
magnitude you're still talking about less than 2% of the U.S.
population.  The BLS estimates there were 16,000 physicists/astronomers
and 77,0000 biological scientists.  So the software/programming and
scientist populations may be roughly comparable.  While the ratios may
be better in some other countries, I doubt that they're much off.

The bottom line is: the markets for PHP/etc. and TML are not too large,
but you seem to be having a hard time convincing those of us who've
taken even a mild interest in TML that it's really needed or is a better
solution than what exists today.

Joe
(Continue reading)

Permalink | Reply | 0 comments |
headers
Richard Huxton | 2 May 2007 16:19
Favicon

Re: Fwd: Re[2]: We all are looped on Internet: request + transport = invariant

Dmitry Turin wrote:
> Good day, Richard.
> 
> RH> With 7 flights it is easy to see if #2 matches #4. With 700 it is not so
> RH> easy to see #2 matches #504. With a tree-structure it is impossible to
> RH> sort leaf nodes without restructuring the tree.
> 
> What is "#2 matches #4" ?

Individual flights might occur in one or more options when building a 
flight-plan.

> What sorting are you imply ?

None.

> To be in safe side:
> TML allow table as particular case of tree
> (when records of one table, i.e. nodes, have not sub-records, i.e. sub-nodes).
> 
> RH> Never heard of eHTML, and I don't believe you can build a public-facing
> RH> website with it unless it's at least as complicated as php.
> 
> I try
> http://lists.w3.org/Archives/Public/public-html/2007Apr/1384.html
> http://lists.w3.org/Archives/Public/public-html/2007Apr/1380.html

Nothing there to build a website with - by which I mean the logic 
required for an interactive website.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Collin Peters | 2 May 2007 17:53
Picon

Re: Dynamic prepare possible in plpgsql?

So if I have an INSERT inside a LOOP in a plpgsql function, it is only
prepared once?

Regards,
Collin

On 5/1/07, Jonah H. Harris <jonah.harris <at> gmail.com> wrote:
> On 5/1/07, Collin Peters <cadiolis <at> gmail.com> wrote:
> > Is it faster to use PREPARE for the various INSERT statements inside a
> > plpgsql function?  Perhaps I am wrong and it does its PREPARE work
> > when the function is parsed.
>
> IIRC, PLpgSQL automagically prepares each statement behind the scenes
> on the first use.
>
>
> --
> Jonah H. Harris, Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation            | fax: 732.331.1301
> 33 Wood Ave S, 3rd Floor            | jharris <at> enterprisedb.com
> Iselin, New Jersey 08830            | http://www.enterprisedb.com/
>

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate
Permalink | Reply | 1 comment |

Gmane