pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArgument variables for select
7+ messages / 5 participants
[nested] [flat]
* Argument variables for select
@ 2000-08-28 08:06 Andreas Tille <tillea@rki.de>
2000-08-28 09:21 ` Re: Argument variables for select Yury Don <yura@vpcit.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Tille @ 2000-08-28 08:06 UTC (permalink / raw)
To: PostgreSQL SQL <pgsql-sql@hub.org>
Hello,
I want to use the following construct in a stored function:
Create Function VarSelect ( varchar, varchar )
returns int
As '
Declare num int ;
Begin
Select Into num Count(*) From $1 Where $2 ;
return num;
End ;
' language 'plpgsql' ;
Could someone please explain who to type the exact syntax so that
I can ship the table to select from as $1 and the condition to select
what as $2? Or do I have to concatenate a string with the whole
select statement and how to call this string? In MS SQL server this
could be done with
Exec ( query )
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Argument variables for select
2000-08-28 08:06 Argument variables for select Andreas Tille <tillea@rki.de>
@ 2000-08-28 09:21 ` Yury Don <yura@vpcit.ru>
2000-08-28 09:53 ` Re: Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-29 12:28 ` Re: Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-29 13:33 ` Re: Re: Argument variables for select Keith Wong <keith@e-magine.com.au>
0 siblings, 3 replies; 7+ messages in thread
From: Yury Don @ 2000-08-28 09:21 UTC (permalink / raw)
To: pgsql-sql@hub.org
Andreas Tille wrote:
>
> Hello,
>
> I want to use the following construct in a stored function:
>
> Create Function VarSelect ( varchar, varchar )
> returns int
> As '
> Declare num int ;
>
> Begin
> Select Into num Count(*) From $1 Where $2 ;
> return num;
> End ;
> ' language 'plpgsql' ;
>
> Could someone please explain who to type the exact syntax so that
> I can ship the table to select from as $1 and the condition to select
> what as $2? Or do I have to concatenate a string with the whole
> select statement and how to call this string? In MS SQL server this
> could be done with
> Exec ( query )
>
> Kind regards
>
> Andreas.
AFAIK it's impossible with plpgsql, but it's possible in pltcl.
--
Sincerely yours,
Yury
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Argument variables for select
2000-08-28 08:06 Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-28 09:21 ` Re: Argument variables for select Yury Don <yura@vpcit.ru>
@ 2000-08-28 09:53 ` Andreas Tille <tillea@rki.de>
2 siblings, 0 replies; 7+ messages in thread
From: Andreas Tille @ 2000-08-28 09:53 UTC (permalink / raw)
To: ; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
On Mon, 28 Aug 2000, Yury Don wrote:
> Andreas Tille wrote:
> >
> > Create Function VarSelect ( varchar, varchar )
> > returns int
> > As '
> > Declare num int ;
> >
> > Begin
> > Select Into num Count(*) From $1 Where $2 ;
> > return num;
> > End ;
> > ' language 'plpgsql' ;
> >
> AFAIK it's impossible with plpgsql, but it's possible in pltcl.
Hmmm, I wonder how many languages I will learn while dealing with
PostgreSQL. What about performance of pltcl compared to C.
I wonder if I just do all my work using C-functions, because I
know C very well and don't want to reach the next limit which
I will possibly face when using pltcl.
I would really like to write all my functions in SQL or PLPGSQL.
If this is really impossible (I just wonder if the construct above
could really not be implemented???), I would prefer C over other
languages, if there are no real drawbacks.
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Argument variables for select
2000-08-28 08:06 Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-28 09:21 ` Re: Argument variables for select Yury Don <yura@vpcit.ru>
@ 2000-08-29 12:28 ` Andreas Tille <tillea@rki.de>
2 siblings, 0 replies; 7+ messages in thread
From: Andreas Tille @ 2000-08-29 12:28 UTC (permalink / raw)
To: ; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
On Mon, 28 Aug 2000, Yury Don wrote:
> > Create Function VarSelect ( varchar, varchar )
> > returns int
> > As '
> > Declare num int ;
> >
> > Begin
> > Select Into num Count(*) From $1 Where $2 ;
> > return num;
> > End ;
> > ' language 'plpgsql' ;
> >
> > Could someone please explain who to type the exact syntax so that
> > I can ship the table to select from as $1 and the condition to select
> > what as $2? Or do I have to concatenate a string with the whole
> > select statement and how to call this string? In MS SQL server this
> > could be done with
> > Exec ( query )
> >
> AFAIK it's impossible with plpgsql, but it's possible in pltcl.
It is really hard to believe that I'm in so very deep trouble with
PostgreSQL. It seems to me that stored procedures are far from beeing
as usefull as I learned them to know in MS SQL server. Once I try
to follow one hint I'm standing in frot of the next even harder problem.
web=# create function testfunc( )
web-# returns int
web-# As '
web'# spi_exec "SELECT count(*) AS $num FROM testtable"
web'#
web'# return $num ;
web'# End; '
web-# language 'pltcl' ;
ERROR: Unrecognized language specified in a CREATE FUNCTION: 'pltcl'. Recognized languages are sql, C, internal and the created procedural languages.
I have installed the pgtcl package of my Debian distribution, so I guess
it should be available. Once more the question: Is it really necessary to
use a further interpreter instead of sticking with SQL commands to use
the original problem.
I have to admit that my boss wonders why I'm switching from a working
solution (MS SQL) to so much trouble :-(. I really hope to convince him
to OpenSource but it's much harder than I expected.
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: Argument variables for select
2000-08-28 08:06 Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-28 09:21 ` Re: Argument variables for select Yury Don <yura@vpcit.ru>
@ 2000-08-29 13:33 ` Keith Wong <keith@e-magine.com.au>
2000-08-29 13:59 ` Re: Re: Argument variables for select hlefebvre <hlefebvre@lexbase.net>
2 siblings, 1 reply; 7+ messages in thread
From: Keith Wong @ 2000-08-29 13:33 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Hi Andreas,
I've worked with MS SQL stored procedures before and they are quite
powerful. Its a shame postgres doesn't have the same
level of features as offered by MS SQL, but apart from this area it is
still a very good database. Perhaps in the coming
versions we will see more stored procedure features? I'm not sure what your
application is exactly but there are ways
to work around the features that are missing.
Good luck with convincing your boss :)
Keith.
At 02:28 PM 29/08/2000 +0200, Andreas Tille wrote:
>On Mon, 28 Aug 2000, Yury Don wrote:
>
> > > Create Function VarSelect ( varchar, varchar )
> > > returns int
> > > As '
> > > Declare num int ;
> > >
> > > Begin
> > > Select Into num Count(*) From $1 Where $2 ;
> > > return num;
> > > End ;
> > > ' language 'plpgsql' ;
> > >
> > > Could someone please explain who to type the exact syntax so that
> > > I can ship the table to select from as $1 and the condition to select
> > > what as $2? Or do I have to concatenate a string with the whole
> > > select statement and how to call this string? In MS SQL server this
> > > could be done with
> > > Exec ( query )
> > >
> > AFAIK it's impossible with plpgsql, but it's possible in pltcl.
>
>It is really hard to believe that I'm in so very deep trouble with
>PostgreSQL. It seems to me that stored procedures are far from beeing
>as usefull as I learned them to know in MS SQL server. Once I try
>to follow one hint I'm standing in frot of the next even harder problem.
>
>
>web=# create function testfunc( )
>web-# returns int
>web-# As '
>web'# spi_exec "SELECT count(*) AS $num FROM testtable"
>web'#
>web'# return $num ;
>web'# End; '
>web-# language 'pltcl' ;
>ERROR: Unrecognized language specified in a CREATE FUNCTION:
>'pltcl'. Recognized languages are sql, C, internal and the created
>procedural languages.
>
>
>I have installed the pgtcl package of my Debian distribution, so I guess
>it should be available. Once more the question: Is it really necessary to
>use a further interpreter instead of sticking with SQL commands to use
>the original problem.
>
>I have to admit that my boss wonders why I'm switching from a working
>solution (MS SQL) to so much trouble :-(. I really hope to convince him
>to OpenSource but it's much harder than I expected.
>
>Kind regards
>
> Andreas.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: Argument variables for select
2000-08-28 08:06 Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-28 09:21 ` Re: Argument variables for select Yury Don <yura@vpcit.ru>
2000-08-29 13:33 ` Re: Re: Argument variables for select Keith Wong <keith@e-magine.com.au>
@ 2000-08-29 13:59 ` hlefebvre <hlefebvre@lexbase.net>
0 siblings, 0 replies; 7+ messages in thread
From: hlefebvre @ 2000-08-29 13:59 UTC (permalink / raw)
To: Keith Wong <keith@e-magine.com.au>; +Cc: Andreas Tille <tillea@rki.de>; PostgreSQL SQL <pgsql-sql@hub.org>
Keith Wong wrote:
>
> Hi Andreas,
>
> I've worked with MS SQL stored procedures before and they are quite
> powerful. Its a shame postgres doesn't have the same
> level of features as offered by MS SQL,
MS SQL is based on source code of Sybase v5. MS bought this source code
to sybase.
Sybase has stored procedure features that even Oracle v7 (and probably
v8) doesn't have,
as returning one ore more seult set(s).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: Argument variables for select
@ 2000-08-29 15:36 Webb Sprague <wsprague100@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Webb Sprague @ 2000-08-29 15:36 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
I think you have to run "createlang pltcl db-foo"
from the command line.
As for your boss, here are three things I can think of
off the top of my head:
1. All new products take some time to learn. If you
can't use Postgres perfectly in a week or so, remember
that you probably can't learn to be an Oracle or
MS-SQL DBA in a week or so either.
2. Postgres is FREE. NO MONEY.
3. Postgres integrates very easily into a Unix
environment.
4. Proprietary software is going the way of the
Do-Do.
Well that's four. At my job, we are building a
database in Postgres to replace an MS-SQL thing. My
version takes 12 hours to upload a months worth of
data. You think that is bad--the SQL Server took 15
DAYS.
Good luck, and sorry for the rant.
W
--- Andreas Tille <tillea@rki.de> wrote:
> On Mon, 28 Aug 2000, Yury Don wrote:
>
> > > Create Function VarSelect ( varchar, varchar )
> > > returns int
> > > As '
> > > Declare num int ;
> > >
> > > Begin
> > > Select Into num Count(*) From $1 Where $2
> ;
> > > return num;
> > > End ;
> > > ' language 'plpgsql' ;
> > >
> > > Could someone please explain who to type the
> exact syntax so that
> > > I can ship the table to select from as $1 and
> the condition to select
> > > what as $2? Or do I have to concatenate a
> string with the whole
> > > select statement and how to call this string?
> In MS SQL server this
> > > could be done with
> > > Exec ( query )
> > >
> > AFAIK it's impossible with plpgsql, but it's
> possible in pltcl.
>
> It is really hard to believe that I'm in so very
> deep trouble with
> PostgreSQL. It seems to me that stored procedures
> are far from beeing
> as usefull as I learned them to know in MS SQL
> server. Once I try
> to follow one hint I'm standing in frot of the next
> even harder problem.
>
>
> web=# create function testfunc( )
> web-# returns int
> web-# As '
> web'# spi_exec "SELECT count(*) AS $num FROM
> testtable"
> web'#
> web'# return $num ;
> web'# End; '
> web-# language 'pltcl' ;
> ERROR: Unrecognized language specified in a CREATE
> FUNCTION: 'pltcl'. Recognized languages are sql, C,
> internal and the created procedural languages.
>
>
> I have installed the pgtcl package of my Debian
> distribution, so I guess
> it should be available. Once more the question: Is
> it really necessary to
> use a further interpreter instead of sticking with
> SQL commands to use
> the original problem.
>
> I have to admit that my boss wonders why I'm
> switching from a working
> solution (MS SQL) to so much trouble :-(. I really
> hope to convince him
> to OpenSource but it's much harder than I expected.
>
> Kind regards
>
> Andreas.
>
__________________________________________________
Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-08-29 15:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-28 08:06 Argument variables for select Andreas Tille <tillea@rki.de>
2000-08-28 09:21 ` Yury Don <yura@vpcit.ru>
2000-08-28 09:53 ` Andreas Tille <tillea@rki.de>
2000-08-29 12:28 ` Andreas Tille <tillea@rki.de>
2000-08-29 13:33 ` Keith Wong <keith@e-magine.com.au>
2000-08-29 13:59 ` hlefebvre <hlefebvre@lexbase.net>
2000-08-29 15:36 Re: Re: Argument variables for select Webb Sprague <wsprague100@yahoo.com>
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