[SQL] PL/pgSQL and PHP 5
I'm having this problem inserting data from my form using PL/pgSQL.
Here is the simplified version of my table and function (this example
does not work, also ):
CREATE TABLE theirry.sample (
staff_id serial PRIMARY KEY NOT NULL,
firstname varchar(100),
lastname varchar(150),
username varchar(35),
identifier varchar(40),
address2 varchar(180),
activated boolean,
activated_keys varchar(32)
);
CREATE OR REPLACE FUNCTION insert_staff_b
(insert_firstname varchar)
RETURNS VOID AS
$$
DECLARE
BEGIN
INSERT INTO theirry.sample
(firstname)
VALUES
(insert_firstname);
RETURN;
END;
$$
LANGUAGE plpgsql;
I have a form with a value firstname then call the query in php
select insert_staff_b('$_POST['firstname']::varchar)
Still I get this error:
Warning: pg_query(): Query failed: ERROR: function
insert_staff_b(character varying) does not exist HINT: No function
matches the given name and argument types. You may need to add explicit
type casts.
Suggestions or maybe a place to read up on this problem.
Thanks in advance,
J
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [PHP] PL/pgSQL and PHP 5
Thanks for the catch. I've tried:
$connection->execute("SELECT
insert_staff_b('$staff[insert_firstname]'::varchar)");
$connection->execute("SELECT
insert_staff_b('".$staff['insert_firstname']."'::varchar)");
None work... I'm scratching my head on this one.
Thanks,
J
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] PL/pgSQL and PHP 5
Tom Lane wrote: PostgreSQL Admin <[EMAIL PROTECTED]> writes: CREATE OR REPLACE FUNCTION insert_staff_b (insert_firstname varchar) RETURNS VOID AS ... Still I get this error: Warning: pg_query(): Query failed: ERROR: function insert_staff_b(character varying) does not exist Sure looks like it oughta work. One possibility is that you created the function in a schema that isn't part of the application's search path. Other than that, look for *really* silly errors, like not creating the function in the same database the application is connected to ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I never thought of that. I look into it. Thanks, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL/pgSQL and PHP 5 - thanks
Thanks, The search path was the problem. Sometimes it's the simple things. Big thanks, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Replace string
Hi, I want to replace a title with dashes and also remove punctuation. e.g, The blue fox's fur. -> The-blue-fox-fur Thanks for any input, J ---(end of broadcast)--- TIP 1: 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
Re: [SQL] Replace string
test=*# select regexp_replace(regexp_replace('The blue fox\'s fur.', ' ', '-',
'g'), '\\.', '');
regexp_replace
The-blue-fox's-fur
(1 row)
Andreas
Thanks for the input.
I came up with this:
REPLACE(regexp_replace('The blue fox\'s fur', '[[:punct:]]', ''), ' ', '-');
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Add constraint for number of chars
Hi, This is seemly simple, but I'm @ a loss this early in the morning. It's best explained this way SAMPLE --- id | serial| username | varchar(100)| constraint username >=8 and username <=100 The problem is that it's characters not integers or numeric. It my constraint correct? Thanks for the help, J ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] pgmemcache
As anyone used pgmemcache? I would like to look more into a but I'm having problems installing the sql. I'm on OS X 10.4 and the sql there are lines causing errors: (e.g. AS '$libdir/pgmemcache', 'memcache_server_add' LANGUAGE 'C' STRICT;) thanks for any input and also will version 1.2 come out of beta? I'm looking to implement it @ work and they are not happy about using beta releases. Thanks, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Constraints for grouping
I have a question that I've thought in my head about using triggers, but I figure to ask people that do SQL more than I. So, I have a table that I want two columns. (1) A featured column which is for only 1 row, once it switched to another row than all other rows must be false title | author_id | categories | featured +---+--+- Thierry Beta Release | 3 | 11 | True Different Approach | 3 | 11 | Ottawa Does Not Heart Hip-Hop | 3 | 11 | (2) A featured column by category and only allow category_feature equal to the number of categories. Is SQL the way to go (and how - ideas), or should I use python for the heavy lifting? Thanks for any input, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Constraints for grouping
> --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > >> CREATE UNIQUE INDEX Only_one_row_true >> ON Your_table ( featured ) >>WHERE featured = true; >> >> Or if you want to only allow 1 featured article per catagory then: >> >> CREATE UNIQUE INDEX Only_one_row_true_per_catagory >> ON Your_table ( catigories, featured ) >>WHERE featured = true; >> > > I forgot the unique part of the DDL. > > Regards, > Richard Broersma Jr. > > Thanks for the information. I will check it out and get back to you. Thanks again, J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Trigger to change different row in same table
I want to write a trigger that updates a different row on the same table. It's pretty basic: before the any row in his table updated I want to set a only row that has the value true to false. I keep getting this error: SQL statement "update theirry.articles set master_featured = false where master_featured = true" PL/pgSQL function "master_featured_maintenance" line 4 at SQL statement My basic trigger: CREATE OR REPLACE FUNCTION theirry.master_featured_maintenance() RETURNS TRIGGER AS $master_featured_maintenance$ DECLARE master_feature boolean; BEGIN update theirry.articles set master_featured = false where master_featured = true; END; $master_featured_maintenance$ LANGUAGE plpgsql; CREATE TRIGGER master_featured_maintenance BEFORE INSERT OR UPDATE ON theirry.articles FOR EACH ROW EXECUTE PROCEDURE theirry.master_featured_maintenance(); Thanks in advance, J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Trigger to change different row in same table
chester c young wrote: > how are you preventing recursion? > > That could be the problem, suggestions? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Foreign Key for multi PK or design question
I have a table in which people will have a number of questions to answer. I want those pk to be placed in my user table. So if a user answers three question I want those 3 pk's in the user table (fk). What should I be doing? Thanks in advance, J ---(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
Re: [SQL] Foreign Key for multi PK or design question
This is my layout so far: CREATE TABLE users ( id serial NOT NULL, --question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED## ); CREATE TABLE questions ( id serial NOT NULL, questions varchar(450) NOT NULL ); CREATE TABLE answers ( id serial NOT NULL, question_id int REFERENCES questions(id) ON DELETE CASCADE, user_id int REFERENCES users(id) ON DELETE CASCADE, answer varchar(450) NOT NULL, created timestamptz NOT NULL ); Originally I wanted to have a foreign key that would be the pk of the question table. So if the user answered Q2, 5 and 6 - the user.fk would store values 2,5,6 - but I have passed most of logic to the answer table. Does this look correct? or most efficient? J ---(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
Re: [SQL] Foreign Key for multi PK or design question
No problems with the design - I was not thinking with the DB hat on at first. I have been working on clustering for a while... just adjusting. Thanks everyone. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Unclosed connections
We are using this bad piece of the software that does not close connections to the postgres server. Is there some setting for closing dead connections? And not TCP/IP keep alive does not work. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Double query
Simple question - but my brain is not working:
This is my table
Column | Type |
Modifiers
---++-
id| integer| not null default
nextval('emr_clinicschedule_id_seq'::regclass)
clinic_id | integer| not null
date | date |
day | smallint |
status| boolean| not null
open | time without time zone |
close | time without time zone |
reason| character varying(64) |
active| boolean| not null
I want to find any value:
SELECT id FROM schedule WHERE clinic_id = '%s' AND date = '%s'
But I want to make sure the clinic exist first then find if the date
exist 2nd.
How do I do that?
Thanks in advance,
J
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] DB Design
I have a inventory system design in which I would like some help with to
see if it's efficient. The products are broken into:
Product tables
Property tables
Configurable Products - this would include colors (i.e. - black, blue
and green) tied to products
I'm thinking of breaking inventory into 2 tables.
Product Inventory
Inventory Adjustment
Should I include the fk of the Configurable Product in the above tables
or break it out further into more tables?
Product Inventory
Inventory Adjustment
--plus--
Product Property Inventory
Inventory Property Adjustment
Thanks for any input,
J
These are my Product tables:
\d cart_product
Table "public.cart_product"
Column | Type |
Modifiers
---+--+---
id| integer | not null default
nextval('cart_product_id_seq'::regclass)
name | character varying(128) | not null
kind | character varying(40)|
sku | character varying(15)|
short_description | character varying(255) | not null
description | text |
category_id | integer | not null
date_created | timestamp with time zone | not null
active| boolean | not null
in_stock | boolean | not null
featured | boolean | not null
ordering | integer |
Indexes:
"cart_product_pkey" PRIMARY KEY, btree (id)
"cart_product_category_id" btree (category_id)
Foreign-key constraints:
"cart_product_category_id_fkey" FOREIGN KEY (category_id) REFERENCES
cart_category(id) DEFERRABLE INITIALLY DEFERRED
\d cart_propertyvariation
Table "public.cart_propertyvariation"
Column | Type |
Modifiers
---+---+-
id| integer | not null default
nextval('cart_propertyvariation_id_seq'::regclass)
properties_id | integer | not null
name | character varying(42) | not null
value | character varying(20) | not null
order | integer |
Indexes:
"cart_propertyvariation_pkey" PRIMARY KEY, btree (id)
"cart_propertyvariation_properties_id" btree (properties_id)
Check constraints:
"cart_propertyvariation_order_check" CHECK ("order" >= 0)
Foreign-key constraints:
"properties_id_refs_id_73bc0a59" FOREIGN KEY (properties_id)
REFERENCES cart_property(id) DEFERRABLE INITIALLY DEFERRED
\d cart_configurableproduct
Table "public.cart_configurableproduct"
Column | Type |
Modifiers
-+--+---
id | integer | not null default
nextval('cart_configurableproduct_id_seq'::regclass)
product_id | integer | not null
variation_id| integer | not null
price_change| numeric(8,2) |
weight_change | integer |
quantity_change | integer |
active | boolean | not null
Indexes:
"cart_configurableproduct_pkey" PRIMARY KEY, btree (id)
"cart_configurableproduct_product_id" btree (product_id)
"cart_configurableproduct_variation_id" btree (variation_id)
Foreign-key constraints:
"cart_configurableproduct_product_id_fkey" FOREIGN KEY (product_id)
REFERENCES cart_product(id) DEFERRABLE INITIALLY DEFERRED
"cart_configurableproduct_variation_id_fkey" FOREIGN KEY
(variation_id) REFERENCES cart_propertyvariation(id) DEFERRABLE
INITIALLY DEFERRED
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Different type of query
I have a table like this: usda=# \d nutrient_data Table "public.nutrient_data" Column | Type | Modifiers -+---+--- ndb_no | integer | not null nutrient_no | integer | not null nutrient_value | double precision | not null data_points | double precision | not null std_error | double precision | src_cd | integer | not null derivation_code | character varying(5) | ref_ndb_no | integer | add_nutr_mark | character varying(2) | num_studies | integer | min | double precision | max | double precision | df | numeric | low_eb | double precision | up_eb | double precision | stat_cmt| character varying(15) | cc | character varying(5) | Indexes: "nutrient_data_pkey" PRIMARY KEY, btree (ndb_no, nutrient_no) Foreign-key constraints: "nutrient_data_derivation_code_fkey" FOREIGN KEY (derivation_code) REFERENCES derivation_code(derivation_code) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_ndb_no_fkey" FOREIGN KEY (ndb_no) REFERENCES food_description(ndb_no) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_nutrient_no_fkey" FOREIGN KEY (nutrient_no) REFERENCES nutrient_definitions(nutrient_no) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_src_cd_fkey" FOREIGN KEY (src_cd) REFERENCES source_code(src_cd) ON UPDATE CASCADE ON DELETE CASCADE when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 13473 | 255 | 57.78 13473 | 262 | 0 13473 | 263 | 0 13473 | 268 | 1033 13473 | 269 | 0 13473 | 291 | 0 13473 | 301 | 5 13473 | 303 | 3.35 13473 | 304 | 24 13473 | 305 |199 13473 | 306 |302 13473 | 307 | 67 13473 | 309 | 4.67 13473 | 312 | 0.131 13473 | 315 | 0.015 13473 | 317 | 10.9 13473 | 318 | 0 13473 | 319 | 0 13473 | 320 | 0 13473 | 321 | 0 13473 | 322 | 0 13473 | 323 | 0.18 13473 | 334 | 0 13473 | 337 | 0 13473 | 338 | 0 13473 | 401 | 0 13473 | 404 | 0.101 I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. how would I write that query. BIG THANKS in advance as I'm lost on this one. J -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Different type of query
Steve Crawford wrote: PostgreSQL Admin wrote: I have a table ... when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 ... I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. Not entirely sure I understand the question. Do you mean that for a given nutrient_no, you want the complete list of nutrient values? If so, it's just: --Example for nutrient_no 203: SELECT ndb_no, nutrient_value from nutrient_data where nutrient_no=203; Cheers, Steve I would like to have multiple values nutrient_no: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 I'm thinking: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208); Now is that the most efficient SQL query? Thanks, J -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Different type of query
Mark Roberts wrote: On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote: I would like to have multiple values nutrient_no: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 I'm thinking: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208); Now is that the most efficient SQL query? Thanks, J It seems that you'd want to do something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and nutrient_no in (203, 204, 208..) You could also grab the most significant 8 nutrients by doing something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 order by nutrient_value desc limit 8 -Mark Thanks Mark! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Tsearch
this is a small sample of the data: short_desc | long_desc --+ CHICKEN,BROILERS OR FRYERS,LEG,MEAT&SKN,CKD,FRIED,BATTER | Chicken, broilers or fryers, leg, meat and skin, cooked, fried, batter Is the best method of search through this data full text search via tsearch or some other method. I'm running version 8.3 say I want to search for chicken skin? Thanks for the advice, J -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Design and Question
Hi,
I have BOTH a sql AND db design question.
I'm creating a cookbook DB with have broken the table into this:
RECIPE TABLE
Column | Type | Modifiers
---+--+--
id | integer | not null default nextval('recipes_id_seq'::regclass)
title | character varying(150) | not null
description | text | not null
servings | integer |
instruction | text | not null
photo | character varying(100) | not null
difficulty | integer |
cuisine | smallint |
course | smallint |
season | smallint |
dietary | smallint |
technique | smallint |
published_date | timestamp with time zone | not null
publishing_rights | boolean | not null
credits | character varying(100) | not null
approved | boolean | default false
cooktime | integer |
preptime | integer |
and this:
RECIPE DIET INFO TABLE
Column | Type | Modifiers
---+--+---
id | integer | not null default nextval('recipes_diet_id_seq'::regclass)
recipe_id | integer | not null
diet | character varying(1) |
RECIPE SEASON TABLE
Column | Type | Modifiers
---+--+-
id | integer | not null default nextval('recipes_season_id_seq'::regclass)
recipe_id | integer | not null
season | character varying(1) |
I can perform is query ->
select title from recipes where id in (select recipe_id from
recipes_season where season in ('P', 'W'));
title
---
ButterFlied Chicken Fillets with Lime
Balsamic Vinegar Chicken with Beans
(2 rows)
select title from recipes where id in (select recipe_id from
recipes_diet where diet in ('P'));
title
---
ButterFlied Chicken Fillets with Lime
How do I combine the two in a query?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
