Thread: Argument variables for select
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
numCount(*) 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.
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
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.
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
thecreated 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.
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.
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).
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/